Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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.    ...
Need Help
  • ams1's avatar
    ams1
    3 years ago

    Hi Anonymous 

     

    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)}"

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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?

 

ams1's avatar
ams1
Responsive Resident
3 years ago

Hi Anonymous 

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

     

     

    • ams1's avatar
      ams1
      Responsive Resident
      3 years ago

      Hi Anonymous 

       

      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