List.Accumulate combine with Text.Contains with dynamic Column header

[franky33]

how to fix error in location2 ? when I use List.Accumulate.

____

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"

 

------

 

I want to get this result

[wdx223_Daniel]

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(1, {{"India"}, {"US UK"}, {"China"}}),

Source = tlb_location,
Custom1 = Table.AddColumn(Source,"location2",each if List.Contains(tlb_filter[Column1],[location],(x,y)=>Text.Contains(y??"",x,Comparer.OrdinalIgnoreCase)) then [location] else null)
in
Custom1

[spinfuzer · Accepted solution]

What you are trying to do is not the best way of doing it and it is uncessarily complicated, but I see what you are trying to do.

Condition needs to be turned into a function.  It has no idea where to take [location] from.

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

We turned the function into a record, so it looks like you are trying to access a field named "ColumnX".  So it should be Record.Field(_,"Column" & Text.From(current))) instead.

Finally, the Table.AddColumn needs to be changed from condition to condition(_) to let the function know that you are passing the current row's record into the function.

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 = (_ as record) => List.Accumulate(
        {1..columnCount},
        null,
        (state, current) => if Text.Contains([location], Record.Field(_,"Column" & Text.From(current))) then [location] else state
 

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