Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
[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:
[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:
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! 🙂
Solved! Go to Solution.
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 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
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |