OLE DB OR ODBC error: We cannot convert the value null to type Table

[ronrsnfld]

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.

[lbendlin · Accepted solution]

Please read this (ideally the entire series too) Power Query M Primer (Part 21): Identifier Scope & Sections | Ben Gribaudo

[Omid_Motamedise]

I use your query and it doesn't make an error for me, can you check the queries?

[ronrsnfld]

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.

[lbendlin]

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.

[ronrsnfld]

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.

[lbendlin · Accepted solution]

Please read this (ideally the entire series too) Power Query M Primer (Part 21): Identifier Scope & Sections | Ben Gribaudo

[ronrsnfld]

Thank you for that.

I will go through it and circle back if I still have questions.

[ronrsnfld]

@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.

[lbendlin · Accepted solution]

These tools use different implementations of the Power Query engine. Power BI has much tighter restrictions around cross partition data bleed etc.