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
MohsinBipu
Helper I
Helper I

Append queries based on excel cell values.

I have some queries and I append all queries into a Master query.

SS23_LL_LP + AH23_LL_LP + SS24_LL_LP = MasterChartLL

But I want that MasterChartLL will be appended based on the Excel table. 

 

My requirement: 

I have an excel table like the below picture.

If the Excel chart is updated SS23, AW23 then, 

MasterChartLL will append SS23_LL_LP + AH23_LL_LP

 

Or

 

If In Excel chart, is updated with SS23, and SS24 then, 

MasterChartLL will append SS23_LL_LP + SS24_LL_LP

 

How can I dynamic append queries based on excel data? 

 

MohsinBipu_0-1678876422865.png

 

 

1 ACCEPTED SOLUTION
ams1
Super User
Super User

Hi @MohsinBipu 

 

They say "eval is EVIL", but in PowerQuery it's an angel 👼!

 

Given:

ams1_0-1679062466371.png

...and TLSeasonFilter is:

ams1_1-1679062505742.png

WHEN MasterChartLL is:

 

let
    Source = TLSeasonFilter,
    #"Added WithSuffix" = Table.AddColumn(Source, "WithSuffix", each [Season] & "_LL_SP"),
    //                                                                          ^^^^^^^^ - all have same suffix, right?
    #"Removed Other Columns" = Table.SelectColumns(#"Added WithSuffix", {"WithSuffix"}),
    TableToList = Table.ToList(#"Removed Other Columns"),
    listOfTables = List.Accumulate(
        TableToList, {}, (final, current) => List.Combine({final, {Expression.Evaluate(current, #shared)}})
    ),
    ret = Table.Combine(listOfTables) // assumes all tables have same header
in
    ret

 

Then you should get what you want - if I understood it correctly. 😉

 

Please mark this as ANSWER if it helped.

 

P.S.: currently the TLSeasonFilter controls what tables get appended. If you want SS24_LL_LP to be always present, just add it to the List.Accumulate seed: instead of "{}", use "{Expression.Evaluate("SS24_LL_SP", #shared)}"

View solution in original post

7 REPLIES 7
ams1
Super User
Super User

Hi @MohsinBipu 

 

They say "eval is EVIL", but in PowerQuery it's an angel 👼!

 

Given:

ams1_0-1679062466371.png

...and TLSeasonFilter is:

ams1_1-1679062505742.png

WHEN MasterChartLL is:

 

let
    Source = TLSeasonFilter,
    #"Added WithSuffix" = Table.AddColumn(Source, "WithSuffix", each [Season] & "_LL_SP"),
    //                                                                          ^^^^^^^^ - all have same suffix, right?
    #"Removed Other Columns" = Table.SelectColumns(#"Added WithSuffix", {"WithSuffix"}),
    TableToList = Table.ToList(#"Removed Other Columns"),
    listOfTables = List.Accumulate(
        TableToList, {}, (final, current) => List.Combine({final, {Expression.Evaluate(current, #shared)}})
    ),
    ret = Table.Combine(listOfTables) // assumes all tables have same header
in
    ret

 

Then you should get what you want - if I understood it correctly. 😉

 

Please mark this as ANSWER if it helped.

 

P.S.: currently the TLSeasonFilter controls what tables get appended. If you want SS24_LL_LP to be always present, just add it to the List.Accumulate seed: instead of "{}", use "{Expression.Evaluate("SS24_LL_SP", #shared)}"

From this step, this is not working. Before, all steps are working. I think, Need to change something. Please advise. Not an expert in M language. 😞 

 

MohsinBipu_0-1679124998366.png

 

 

 

Hi @MohsinBipu 

 

Can you please share the error?

Ohhh, Sorry, My mistake. I have found the reason.  Basically, I update the excel wrong and There are no Power query exists. that's why This error is coming. Anyhow, can I avoid this error with  formula update, like if no query exists as per the excel update, then the formula will avoid those query?

MohsinBipu_1-1679289240663.png

 

Hi @MohsinBipu 

 

First of all, if it worked and it helped, please mark my first reply as answer 🤗

Second, I think you can put a "try" before Expression.Evaluate... and after an "otherwise null" - and see if it works.

something like below?

 

let
    Source = TLSeasonFilter,
    #"Added WithSuffix" = Table.AddColumn(Source, "WithSuffix", each [Season] & "_LL_SP"),
    //                                                                          ^^^^^^^^ - all have same suffix, right?
    #"Removed Other Columns" = Table.SelectColumns(#"Added WithSuffix", {"WithSuffix"}),
    TableToList = Table.ToList(#"Removed Other Columns"),
    listOfTables = List.Accumulate(
        TableToList, {}, (final, current) => List.Combine({final, {try Expression.Evaluate(current, #shared)} otherwise null})
    ),
    ret = Table.Combine(listOfTables) // assumes all tables have same header
in
    ret

 

 

Hi @MohsinBipu 

 

Something like:

let
    Source = TLSeasonFilter,
    #"Added WithSuffix" = Table.AddColumn(Source, "WithSuffix", each [Season] & "_LL_SP"),
    //                                                                          ^^^^^^^^ - all have same suffix, right?
    #"Removed Other Columns" = Table.SelectColumns(#"Added WithSuffix", {"WithSuffix"}),
    TableToList = Table.ToList(#"Removed Other Columns"),
    listOfTables = List.Accumulate(
        TableToList, {}, (final, current) => List.Combine({final, {try Expression.Evaluate(current, #shared) otherwise null}})
    ),
    ret = Table.Combine(listOfTables) // assumes all tables have same header
in
    ret

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.

Top Solution Authors
Top Kudoed Authors