Show
Thursday The issue for me was that across 50+ workbooks, the first sheet was not always named the same. The soultion was to dynamically pick up the first sheet on each workbook, rather than taking the hard coded variable from the sample combine effort.
> Comment out the "data_sheet" line, and add the "FirstSheet" line. Bingo. let Source = (Parameter1 as binary) => let Source = Excel.Workbook(Parameter1, null, true), FirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data], // Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(FirstSheet, [PromoteAllScalars=true]) in #"Promoted Headers" in SourceMessage 39 of 39 1,164 Views
04-17-2022 11:49 PM - Click duplicate on source on the right ( Applied step) - It show a table--> You can see at the Hiddens column--> It's "False" - Click on "Table" on Data column. - It's show a msg box (Pic 2) --> Choose continuos It's solved Message 36 of 39 60,327 Views
02-24-2022 04:22 PM I had the same error message connecting to a tabular model via visual studio. When I validated the query, "The query statement is valid." Going into the "Design" view is when I got the error. I fixed it from the "Query" menu, selected refresh query, and it worked. Message 34 of 39 83,807 Views
01-13-2022 10:59 AM I had the same problem today. This is how I fixed it: Make sure to go under advanced editor on Power BI I conected my excel workbook as tables inside Power BI, so in this case you need to make sure that the file location, file name and most important, table name is correct. ______________ let _______________ How do you change a table name in excel?Go under Formulas Tab > Name Manager Select the table you want and click edit. This name should be the one you have to insert under Power BI conection above Make sure to do this to every imported table on you model Message 33 of 39 103,037 Views
04-23-2021 05:12 AM I started having this error after populating the file path with daily workbooks where the name of the sheet wasn't the same as my query / Transform Sample File (My power query reads from many workbooks wich are the same except for the name of the sheet which is named after each workbook) The query was attemping to find in all the workbooks the same sheet name as in my data transformation example. I tried to sorted it out by removing the Navigation stept in the query but did not work. How did it work? >In the Transform Sample File, I opened the Advanced Editor and changed the name of the Sheet to zero It was> Source = Excel.Workbook(Parameter1,null,true) #"03_21_4_20_Sheet"=Source {[Item="03__21_4_20",Kind="Sheet"]}[Data], I changed it to > Source = Excel.Workbook(Parameter1,null,true) #"03_21_4_20_Sheet"=Source {0}[Data], Regards, Message 32 of 39 198,375 Views
02-02-2021 01:10 AM If you are combining Excel Tables: All tables' names should be the same If you are combining Excel Sheets: All sheets' names should be the same As simple as that Thanks. John Message 31 of 39 228,816 Views
09-01-2019 11:09 PM 1. First locate the error and find the problematic step,For me the error occurs in "Invoke Custom Function" step, and error is :"An error occurred in the ‘Transform File’ query. Expression.Error: The key didn't match any rows in the table." 2. Find the "Transform File" Function = (Parameter1) => let Source = Excel.Workbook(Parameter1, null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]) in #"Promoted Headers"This code is created according to the excel of the example, prompting other workbooks in our folder, should also be named “sheet1” 3.So, just rename all workbooks under the folder sheet's name to "sheet1", this problem can be solved. Message 28 of 39 242,858 Views
02-06-2019 02:27 AM @altomost - Thank you very much, that was exactly what I did and removing the Navigation fixed it. It is a shame you can't import views from SQL Server, instead you have to import a table and then edit the query to act like a view. That was when I hit this error. Chris Message 21 of 39 244,690 Views
08-12-2018 08:58 PM If you are using Tables/Views from SQL Server, check whether the power bi credentials have the permission to read from the table/view you have linked. 1. Go to "Options and settings" -> "Data source settings" -> "Edit Permissions" 2. 3. Cross check in the database whether this user role has the permission to access the table/view. 4. Go to SQL server 5. Right click on the table/view. Click "Poperties" ->Go to "Permissions". 6. 7. Add the user role/ give permission accordingly. 8. Go to Power BI and refresh. Message 20 of 39 245,536 Views How do you fix the key didn't match any rows in the table?There are two ways to fix the error:. Go to the Excel workbook and change the name of the sheet called “Hello” back to “Sheet1” or.. In either the Advanced Editor or the Formula Bar edit the M code and replace the reference to “Sheet1” with “Hello” in the second step of the query:. How do you resolve Expression error in Power Query?Here are the steps I took:. Get Data from 2 different folders, "Price Reports" and "Product Reports".. Clean and transform data from the files in each folder.. Merge the files from both folders.. Load to Table and Pivot Chart.. How do I remove query errors?Remove rows with errors. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.. Decide which columns for which you want to remove errors. ... . Select Home > Remove Rows > Remove Errors.. How do I change the key in Power Query?In Query Editor:. Do your Unpivoting work.. Add Column; Index Column; From 0 or 1. ... . Create a copy of the column that the editor thinks is your primary key. ... . Delete the column that the editor thinks is your primary key (so delete "Primary" column in this example.) ... . Apply your changes.. |