Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
Hi @MohsinBipu
They say "eval is EVIL", but in PowerQuery it's an angel 👼!
Given:
...and TLSeasonFilter is:
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)}"
Hi @MohsinBipu
They say "eval is EVIL", but in PowerQuery it's an angel 👼!
Given:
...and TLSeasonFilter is:
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. 😞
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?
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