Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
This error pops up when I select Apply in Power Query in Power BI desktop.
I have a project which involves processing a number of PDF files to extract relevant information.
I have a report file (fnReport) which will combine about 60 of the 100-150 files that are in the project.
If I hard-code the query names, there is no problem either combining the tables, or Applying them back to the Power BI desktop.
I though it would simplify things, rather than hard-coding all of the names, to generate a list of queries (using #sections) and then I could simply select the particular queries I wanted to include in the report.
There are no errors in any of the queries. But when I click `Apply`, an error pops-up.
If I replace `AccountsList[Value]` with a hard-coded list of the relevant queries (all of which output a table), there is no error in selecting `Apply`.
Any thoughts as to what is going on? I suspect there is some issue having to do with the reference being generated by the `#sections` keyword.
To reproduce error
in Power BI, paste the following into three separate blank queries
Table1
Table.FromColumns(
{{"abc","def"}}
& {{"efg","jkl"}},
type table[A=text, B=text])
myList
let
Source = #sections[Section1],
#"Converted to Table" = Record.ToTable(Source),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "Table1"))
in
#"Filtered Rows"
myReport
let
report = Table.Combine(myList[Value])
in
report
Deselect `enable load` on `Table1` and `myList`.<br>If you don't do this, you will simply get more error messages
Then select `Apply` or `Close and Apply`
Even though there are no errors in the queries, this action will return the error message:
If, in `myReport`, you replace `myList[Value]` with `{Table1}`, no error occurs and `myReport` is successfully applied.
Solved! Go to Solution.
Please read this (ideally the entire series too) Power Query M Primer (Part 21): Identifier Scope & Sections | Ben Gribaudo
These tools use different implementations of the Power Query engine. Power BI has much tighter restrictions around cross partition data bleed etc.
I use your query and it doesn't make an error for me, can you check the queries?
I using Power BI Desktop Version: 2.136.1202.0 64-bit (September 2024).
And yes, I just copy/pasted what I posted into the Power Query Advanced Editor, and when I choose "Close and Apply", the error messages pop-up. This time they popped up for all three queries, even though I had disabled load on two of them as I noted in my instructions.
I though it would simplify things, rather than hard-coding all of the names, to generate a list of queries (using #sections) and then I could simply select the particular queries I wanted to include in the report.
Dynamic number of queries is not supported in Power Query. Each partition needs to be predefined.
Run this whole thing in a single partition and add the query identifier as a column.
Can you elaborate?
I'm not sure what you mean, nor how to implement what you say.
Could you perhaps create an example using my three sample queries as a base, to create something that will not produce the Power BI error when I close and apply, and avoid my having to hard code every query I want to include in the final report?
Although, for my purposes, I have solved the problem by transferring all the queries to Excel. In Excel, I can apparently select my list of relevant tables from the #sections, and can Close and Load successfully to the worksheet.
Still curious about this business in Power BI.
Please read this (ideally the entire series too) Power Query M Primer (Part 21): Identifier Scope & Sections | Ben Gribaudo
Thank you for that.
I will go through it and circle back if I still have questions.
@lbendlin I went through that excellent reference. (Just the one chapter. I have read some of the others).
What I don't understand, though, is why it does work in Power Query (whether accessed via Power BI or Excel) and it can be Loaded in Excel to a worksheet but cannot be Applied in Power BI.
These tools use different implementations of the Power Query engine. Power BI has much tighter restrictions around cross partition data bleed etc.