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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Mike40uk
Regular Visitor

Check table exists before combine

I am trying to combine a list of tables which may change depending on the amount of orders.

I have managed to test each table with 'try' and ended up with a list of tables that are valid I need to combine. (Query1)

 

 

When I get to the combine I can not use this list  it either returns the table itself or comes up with the error 'We cannot convert a value of type Table to type List.'

let
    Source = Table.Combine({Query1})
in
    Source

 

 

 

If I do it manually as below it works.

 

let
    Source = Table.Combine({UKAHL,ITBVG,UKJR})
in
    Source

 

 

1 ACCEPTED SOLUTION

It looks like your Query1 doesn't result in a list of Tables, like in the following example:

 

List Of Tables.png

Specializing in Power Query Formula Language (M)

View solution in original post

5 REPLIES 5
MarcelBeug
Community Champion
Community Champion

Probably you should remove the curly braces:

 

let
    Source = Table.Combine(Query1)
in
    Source
Specializing in Power Query Formula Language (M)

Thanks for comments,  I have tried that and that is when I get the error

 

Expression.Error: We cannot convert a value of type Table to type List.
Details:
    Value=Table
    Type=Type

It looks like your Query1 doesn't result in a list of Tables, like in the following example:

 

List Of Tables.png

Specializing in Power Query Formula Language (M)

Thank you, that pointed me in the right direction to solve what I needed.

 

What I ended up with was the below, which trys each table and if no error puts in the table name otherwise adds a blank. Then when I use the table list in my combine query I filter out the blank table names first.

Probaly not the best way but it works for me.

 

let



        value1 = if (try UKAHL)[HasError] =true then "" else UKAHL,  
        value2 = if (try UKJR)[HasError] =true then "" else UKJR,
        value3 = if (try NLJR)[HasError] =true then "" else NLJR,
        value4 = if (try ITBVG)[HasError] =true then "" else ITBVG,
        value5 = if (try ITAHL)[HasError] =true then "" else ITAHL,
        value6 = if (try IEJR)[HasError] =true then "" else IEJR,
        value7 = if (try IEAHL)[HasError] =true then "" else IEAHL,
        value8 = if (try FRBVG)[HasError] =true then "" else FRBVG,
        value9 = if (try ESBVG)[HasError] =true then "" else ESBVG,
        value10 = if (try DEBVG)[HasError] =true then "" else DEBVG,
        value11 = if (try DEBVG1)[HasError] =true then "" else DEBVG1,
        value12 = if (try DEAHL)[HasError] =true then "" else DEAHL,
        value13 = if (try BEJR)[HasError] =true then "" else BEJR,

  Table1 =#table(1,{{value1}}),Table2 =#table(1,{{value2}}), Table3=#table(1,{{value3}}),Table4 =#table(1,{{value4}}),Table5=#table(1,{{value5}}),Table6 =#table(1,{{value6}}),Table7 =#table(1,{{value7}}),Table8 =#table(1,{{value8}}),Table9 =#table(1,{{value9}}),Table10 =#table(1,{{value10}}),Table11 =#table(1,{{value11}}), Table12 =#table(1,{{value12}}), Table13 =#table(1,{{value13}}),
Tablelist = {Table1,Table2,Table3,Table4,Table5,Table6,Table7,Table8,Table9,Table10,Table11,Table12,Table13}
in
    Tablelist

Possibly code like the following - shortened - example will also work for you?

 

let
    TableList =
         {try UKJR otherwise #table(1,{}),
          try ITBVG otherwise #table(1,{})}
in
    TableList
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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