"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.
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!
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!
