"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.
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?
Excel Mac - get external data from API - how to get only the data I want, and not extra stuff
I have a txt file (query1.txt) saved in my Excel Queries folder that contains the following text (it's displaying as a link in this forum, but in my plain txt file it's just the text): https://api.quadrigacx.com/v2/ticker?book=btc_cad (It's followed by a few line breaks since if I don't do that, Excel can't read the file.) Then I go to my workbook in Excel (for Mac), select cell A1, then go to Data > Get External Data > Run Saved Query and select query1.txt. However, the data that it then displays in cell A1 looks like this: {"high":"9370.00","last":"9329.88","timestamp":"1510758340","volume":"434.66143942","vwap":"8737.12428249","low":"8439.98","ask":"9349.49","bid":"9266.01"} However, the only piece of that I want to display in the cell A1 is the current value for "last", 9329.88. Is there a way to get Excel (for Mac) to only display the value I want, or am I stuck having to manually copy and paste that figure from the long string of data it's grabbing? I don't know how to code, but I can follow directions :) If anyone can tell me how to make Excel pull out the data for only the "last" (i.e. 9329.88 in this example), that would be awesome! Thanks in advance to all who try to help me :)Third parameter for Excel.Wokbook() function - what's that?
This function always had two parameters as it is documented for today Excel.Workbook(workbook as binary, optional useHeaders as nullable logical) as table https://msdn.microsoft.com/en-us/library/mt260859.aspx However, with the latest PQ update connector to Excel file generates 3 parameters, it looks like Source = Excel.Workbook(File.Contents("TheBook.xlsx"), null, true) Second parameter (here is null) works as expected. What is the third parameter for i was not able to recognize, it affects nothing in my short tests. Perhaps it's somehow connected with modified Combined Binaries introduced in Jan/17 update. It auto-generates "Transform ..." queries which are based on Excel.Wokbook(). That's only the guess. So far have no idea what to do with third parameter. Except to remove it at all - otherwise people who have no recent G&T updates (more exactly, Excel 2016 updates - majority of them even don't know what G&T exists within Excel) receive an error what some functuion receives 3 parameters instead of 1 or 2...
Get & Transform credentials prompt
I have the same issue as the gentleman from Ratheon. I have built a file that successfully queries a client's Sql database, and now want to give that file to one of their accounting folks to refresh and use on a daily basis, without my having to do it for them. When I run it on my machine over VPN, after the initial build, . . . it never asks me again to give it id and password. When I give the same file to them, it is asking them for the information. I am wanting this file to work automatically for them without it asking them for the information, it uses a system id to get the data. If I edit the query, and go to data source settings, then edit permissions, it brings up the SQL Server Database Connection information screen, with the userid filled in and the password asterisked out, ie, it KNOWS the information, and in theory, it's stored with the query in the sheet (or not, . . I guess . .. . . cause when I pass it to them, it asks them). How the heck does this really work, and where is this data stored such that it looks like it knows the info needed when I look at it, but doesn't know it when I send them the file? Nothing in the online documentation seems to refer to how this works and/or how to do this so that I can send my wonderfully working query to the end user to carry forward with. Arrrgh maties, I am struggling to find anything that will help solve this issue!
