Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vpenas
Frequent Visitor

[Expression.Error] Cannot convert the value null to type Table

I am trying to combine some queries into one by using the Table.Combine() function.

 

If I explicitly write the name of each query (e. g., Table.Combine({#"Name of query 1", #"Name of query 2"})) and then apply the changes, everything works fine.

 

However, since I want to make it dynamic, instead of writing a list of names, I pass the function a list of tables generated in a previous step: 

vpenas_0-1656010802930.png

[ALT TEXT: screenshot of a table of two columns ("Name" and "Value"), with four records. "Name": COMPRAS Y GASTOS 2019... until COMPRAS Y GASTOS 2022. "Value": an object of type Table in each of the four cells].

So after I get this table, the next step is: = Table.Combine(PreviousStep[Value]). Note that Value is the name of the column that contains the tables. Apparently, by doing so this column of a table containing tables is converted to a list containing tables. This works fine (I can preview the resultset) until I hit that Apply changes button. When I do it, this message pops up:

vpenas_2-1656010980715.png

[ALT TEXT & translation: Error pop-up message: "Load. Anexar1 (which is the name of the query): OLE DB or ODBC error: [Expression.Error] Cannot convert the value null to type Table"].

 

I've tried many many things, but none of them worked.

 

I had a look at these threads: https://community.powerbi.com/t5/Desktop/We-cannot-convert-the-value-null-to-type-Table/td-p/391064, https://community.powerbi.com/t5/Desktop/We-cannot-convert-the-value-null-to-type-table/m-p/346056, but it didn't work. I've tried other approaches as well.

 

 

Further information:

  • Power BI Desktop version: 2.106.582.0 64-bit (June 2022)
  • Data source: combining existing queries that come from a single Excel file.
  • Steps followed to get that list of tables that I pass the Table.Combine() function:

let
Origen = #sections[Section1],
#"Convertido en tabla" = Record.ToTable(Origen),
#"Errores quitados" = Table.RemoveRowsWithErrors(#"Convertido en tabla", {"Value"}),
Personalizado1 = Table.SelectRows(#"Errores quitados", each Text.StartsWith([Name], "COMPRAS Y GASTOS")),
Personalizado2 = Table.Combine(Personalizado1[Value])
in
Personalizado2

 

Basicly, I access all the queries I have (with the #sections keyword), convert it to a table, remove possible errors, filter to get the queries I want (the ones starting by "COMPRAS Y GASTOS") and then try to combine the queries).

 

 

If you have any suggestions or the solution to this issue, I will be so grateful.

 

THANK YOU for your time and help! 🙂

1 ACCEPTED SOLUTION
vpenas
Frequent Visitor

Hi Henry, thanks for your help. I didn't resolve the problem, but I worked around it, by modifying the data source. Here is my post on another site, where I explain how I did it: https://stackoverflow.com/questions/72735648/power-query-expression-error-cannot-convert-the-value-n...

 

Thank you though!

Víctor

View solution in original post

3 REPLIES 3
vpenas
Frequent Visitor

Hi Henry, thanks for your help. I didn't resolve the problem, but I worked around it, by modifying the data source. Here is my post on another site, where I explain how I did it: https://stackoverflow.com/questions/72735648/power-query-expression-error-cannot-convert-the-value-n...

 

Thank you though!

Víctor

Hi @vpenas ,

 

Thanks for your feedback. If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly.😊

 

Best Regards,
Henry

v-henryk-mstf
Community Support
Community Support

Hi @vpenas ,

 

In the query editor, the logical column should appear in its column header as having a red X and a green check mark. Maybe you can track it that way. Another way is to filter out the null values on each column until you find the correct column.

Alternatively, you can check out similar solutions.

Solved: Cannot convert value to type table - Microsoft Power BI Community


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.