Share via

Excel Power query M use List.Accumulate and Text.Contains dynamic Column head

Anonymous
2023-11-21T08:34:34+00:00

I try to get location2 result as {"US UK", "I love US UK", "US UK", null, "UK India", "Australia China", null, null} by power query M in excel below

can you tell me why error in location2 column.

______________________

let 

tlb\_location = #table( { "location", "value" }, 

    {{ "US UK", 1 }, { "I love US UK", "21" },{ "US UK", 1 }, { "UK", "13" }, 

    { "UK India", 41 }, { "Australia China", "15" }, { "", "61" }}), 

tlb\_filter = Table.Transpose(#table( { "filter" }, {{"India"}, {"US UK"}, {"China"}} )), 

Source = tlb\_location, 

#"Filtered Rows" = Table.SelectRows(Source, each ([location] <> null)), 

#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"location", type text}, {"value", Int64.Type}}), 

#"Appended Query" = Table.Combine({#"Changed Type", tlb\_filter}), 

columnCount = Table.ColumnCount(tlb\_filter), 

columnList = List.Transform( 

    {0..columnCount-1}, 

    each "Column" & Text.From(\_ + 1) 

), 

#"Filled Up" = Table.FillUp(#"Appended Query", columnList), 

condition = List.Accumulate( 

    {1..columnCount}, 

    null, 

    (state, current) => if Text.Contains([location], "Column" & Text.From(current)) then [location] else state 

), 

#"Added Conditional Column" = Table.AddColumn(#"Filled Up", "location2", each condition)

in 

#"Added Conditional Column"

_________________________

Microsoft 365 and Office | Excel | For education | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144K Reputation points Volunteer Moderator
    2023-11-21T12:07:53+00:00

    let
    tlb_location = #table(
    {"location", "value"},
    {
    {"US UK", 1},
    {"I love US UK", "21"},
    {"US UK", 1},
    {"UK", "13"},
    {"UK India", 41},
    {"Australia China", "15"},
    {"", "61"}
    }
    ),
    tlb_filter = #table({"filter"}, {{"India"}, {"US UK"}, {"China"}}),
    Source = Table.AddColumn(
    tlb_location,
    "match",
    each
    let
    // Get the location
    l = Record.Field(_, "location"),
    // Loop through all filters and return the match if any
    r = List.Accumulate(Table.Column(tlb_filter, "filter"), null,
    (s, c) => if Text.Contains(l, c, Comparer.OrdinalIgnoreCase) then c else s
    )
    in
    r
    )
    in
    Source

    0 comments