Power Query adds an apostrophe to cells queried from an Excel table
Excel 2010, Power Query version 2.48.4792.941 32-bit. I have a table (call it Table_Source) in an Excel file (call it source.xlsm) that I query from another Excel file (call it destination.xlsm). The source table contains multiple columns, some formatted as text, some formatted as general. In destination.xlsm, I use Power Query to query Table_Source. Some of the queried columns are written into destination.xlsm with a leading apostrophe in each cell, even though the columns are formatted as text. This wreaks havoc with some VBA code. How do I get Power Query to not append the leading apostrophe? Thank you!
Reading .csv stored in Azure Blob Storage from Excel
I've been able to create a storage account, then a container, than a blob storing a .csv file. I've successfully been able to download the .csv file from Azure itself and using Python as well via the azure-storage package. This is to confirm that external sources (i.e. Python) are able to import/export blobs. The .csv stores a numeric table with header in the first row. The second step is to import the same data in Excel 2016. The steps that I'm following from Excel are: New Query --> From Azure --> From Microsoft Azure Blob Storage --> provide <Account_name> and <Key> --> Navigator. From here, I can see that dataset.csv file in the container. However, clicking the button "load" in the Navigator only allows to download the table in the previous picture in excel, and not the data stored in those files. I've explored the add ons "Design" and "Query" to look for a button to download the data stored in those blobs but unsuccessfully. Question: how to import the numberic data stored in one of the .csv in Azure Blobs listed in the Wizard?
Needless Feature Constantly Interferes with Data Entry While Using Excel
Just working on a simple Excel task.... Hello, I often have to correct data in excel and there is a feature in excel that is interfering with my work. I'm sure that other people that do any kind of data entry have come across this feature and are simply working around it. It took me some time before I got fed up and decided to write you guys. So, what happens? I will double click on a cell in excel and my cursor goes to the bottom of the excel sheet data column. Maybe I accidently clicked on a line but I do not want to have to reset my cursor position every time I intend to double click on a cell. It is very annoying and it breaks my work momentum. It's like every other time I double click something in excel, my cursor just goes to the bottom of that excel column in that worksheet. I don't understand why this feature exists. It makes my life harder than it needs to be. Seriously, why? Someone, please do something about this."The input couldn't be recognized as a valid Excel document."
Such alert could appear when you try to use Power BI connector on Excel file. It's understandable if the source file is corrupted and can't be opened in Excel. However, it looks strange if Excel opens the file in question and shows nothing wrong. Based on our experience above is usually means what something is wrong with XML scheme of the Excel workbook. Mushup trace (Data->New Query->Query Options->Diagnostics->Enable tracing) could give some additional information, but often not enough to find the reason. We had two main scenarios XML scheme is not complete Usually if Excel file was generated by third-party tool. Such tool could generate quite limited XML scheme which is enough to open the file in Excel and to work with it, but not enough for Power BI connector. As an example, trace log shows [DataFormat.Error] The input couldn't be recognized as a valid Excel document.\r\nStackTrace:\n… … [DataFormat.Error] We couldn't find a part named '/xl/sharedStrings.xml' in the Excel package.\r\nStackTrace:\n… Such case is easy to fix – it's enough to open the file in Excel and save it (without any changes) – Excel is clever enough to fix the scheme. For the routine regular tasks we use poweshell script which does exactly the same in background. There is the link within Excel file which is not recognizable as valid Usually if Excel file is synced/kept with some cloud storage. One of the variants, wrong link could appear with copy/paste from another such file. That could be active link in one of the cells; or the link within conditional formatting formula; or even the link which actually isn't used by Excel but kept somewhere inside the scheme. For example, in one of the files I found in Data->Consolidate->All references the link like '\\drive.tresorit.com@7235\Tresors….\[file.xlsx]Sheet'!$AC$6:$AC$357 on the file which was deleted long ago and isn't used, but for some strange reason the link was kept within the scheme. Unfortunately for such case trace log doesn't give enough information to localize the issue, it looks like [DataFormat.Error] The input couldn't be recognized as a valid Excel document.\r\nStackTrace:\n… … nExceptionType: System.UriFormatException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: Invalid URI: The hostname could not be parsed.\r\nStackTrace:\n Perhaps I have not enough knowledge for more straight forward localization of the problem, but the only way is to exclude Excel file parts one by one and check if the issue disappeared. Another way could be to unzip Excel file and check if wookbook.xml or sheetNN.xml have something suspicious inside.
Join two columns of data in a recursive manner
I have two columns that I want recursively joined. Column 1: regions (KY, PA, MI) Column 2: plant resources (Resource 1, Resource 2, Resource 3) Column 3: company resources (KY-Resource 1, KY-Resource 2, KY-Resource 3, PA Resource 1...) I expect to update add rows to columns 1 and 2. So would like Column 3 to update by adding the additional rows. I figured Get & Transform would be the way to do this, but can't seem to get that to work.
Excel 2013 - Import From Web with Basic Auth - Display issue
Hello all, Im having an issue with Import From Web option in Excel 2013, which occurs only when loading an Xml file through a Web Url that needs a Basic Authentication. Loading an XML file through a web service of mine, I have some data nicely displayed as a table in Excel. But loading the same XML Data with an API with Basic Auth, the Load From Web form ask me for the login/pwd, it loads well the data, but not nicely displayed as a table in Excel, Is there any workaround for this issue? Thx for any help, opinion on this, michael
Replicating Conditional Formatting
In a spreadheet, I have conditional formatting so that in a row, the lowest value is formatted with a red font colour. I can apply this row by row either from scratch or using "Format Painter" but this needs to be undertaken a row at a time. Please can someone tell me if there is there a way of quickly applying this row conditional formatting across the spreadsheet so that the lowest value in each individual row is highlighted? I'm sure there must be an easy way but haven't yet found it.Problem sharing worksheet with SQL credentials on Power Pivot
I created an Excel 2017 worksheet with a Power Pivot data connection to a SQL Server on our corporate network. I want to let a coworker use the worksheet to view and refresh the data. From the Data tab I selected New Query-> From Database ->From SQL Server Database I then entered the server and database names and a custom query. I set up the connection using Windows credentials, Privacy Level None, Encryption Off. Then loaded the data into a worksheet tab. I emailed the worksheet to my coworker. This user can run the query in the worksheet in SQL Management Studio so we know he has access to the database. When he tries to refresh the data in the worksheet it gives him an error : "We couldn't authenticate with the credentials provided". On his computer we selected Use Alternate Credentials and entered his windows login creds. We got he same error. Thank you in advance for your assistance MattUsing GetData on JSON file only gives two columns without data
Hello, I'm using Excel 2016. I tried to use the Data ribbon to Get Data\From File\From JSON and imported a file that looks like this: { "categories": [ { "name": "Cheap & Cool Gadgets", "url": "http://www.chinavasion.com/china/wholesale/Electronic_Gadgets/", "image": "http://cdn.chv.me/images/_zSqANqA.jpg", "subcategories": [ { "name": "Cool Gadgets", "url": "http://www.chinavasion.com/china/wholesale/Electronic_Gadgets/Cool_Gadgets/", "image": "http://cdn.chv.me/images/zASqnkuu.jpg" }, { "name": "Camera Equipment / Accessories", "url": "http://www.chinavasion.com/china/wholesale/Digital_Cameras-Camcorders/Camera_Accessories/", "image": "http://cdn.chv.me/images/3qsmbvZc.jpg" }, { "name": "Laser Gadgets & Measuring Tools", "url": "http://www.chinavasion.com/china/wholesale/Electronic_Gadgets/Laser_Gadgets/", "image": "http://cdn.chv.me/images/TWvnHhzt.jpg" }, { "name": "Special LED Lights", "url": "http://www.chinavasion.com/china/wholesale/LED_Lights/Special_LED_Lights/", "image": "http://cdn.chv.me/images/rVbp3Cxi.jpg" } ] } ] } After the Query Editor opens, I click Convert\Into Table and then I click Close and Load. It shows this screen: Does anyone know how to get it to show the actual data? I validated the json data here: https://jsonformatter.curiousconcept.com/ Thanks, Tony
