Ошибка power query expression error

SOLVED USING A DIFFERENT APPROACH (see at the end)

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:

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:

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

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

A DIFFERENT APPROACH
What I wanted to do was merge tables that came from an Excel file, each of them referring to a year (2019, 2020, 2021, 2022). But I also wanted the combined table to update when new sheets were added on Excel (2023, 2024…).

I’ve tried many different approaches, like generating a dynamic list (from 2019 until the current year)… but for some reason none of them worked, even though the code apparently is correct.

So my new approach has been to create a sufficient amount of Excel sheets for the coming years (that are now empty, but when the new year comes the information will be filled in there), to create the queries referring to those sheets (they return empty tables) and merging those existing (but empty) tables with the ones from 2019-2022. This way, when data from 2023 is filled in in the sheet, the query is updated and it works.

It’s a shame I couldn’t actually solve the original problem I had, but this approach works.

I’m working in an ERP reporting project and I have relatively complex PBI and PQ set with dozens of tables and processing steps.

Everything were fine until yesterday when I started to get error Expression.Error: We cannot convert the value null to type Number from several different phases of the processing steps. What makes no sense is that the phases where the error occurs is not how it normally is.

So, this is the end result from one of the tables I’m processing

In the middle of the process eveything works fine. Here is the column item list I suspect is the reason for the errors:

No problems there. This is the applied step #16

The next step #17 is about filtering some rows based on completely other column and which does not have anything to do with the salesorderItem column:

«= Table.SelectRows(#»Added invoicingPeriodWorkingDays», each [invoicingPeriodStarts] <= [invoicingPeriodEndDate])»

And now, when I look at the salesorderItem column after the step #17 I get :

There it is. 

Anyone having seen similar behavior? Is this a memory related problem? Is there any way to get better insight what could cause this?

Jani