Powerquery extract values from list in column B based on another columnA value-CodePudding

I have two columns A and B

A is a normal column. Let's say it may have any of the following colors: black, white, orange B each B record contain a list. Let's say "white shirt", "white trousers", "orange t-shirt"

I'm trying to get in column B the items related to the color in column A.

If A = white, then I want in one cell "white shirt" and "white trousers".

If I hard code "white", it works, but I can't pass [A] to Text.Contains (or I don't know how)

= Table.TransformColumns(#"Added Custom", {"B", each Text.Combine(List.Transform(List.Select(_,each Text.Contains(_,"white")), Text.From), "#(lf)"), type text})

Please, I appreciate any help.

CodePudding user response：

The trick is to set value of the first column equal to a variable before starting to work with the list in the second column

so for your example

= Table.TransformColumns(#"Added Custom", {"B", each let search = [ColumnA] in Text.Combine(List.Transform(List.Select(_,each Text.Contains(_,search)), Text.From), "#(lf)"), type text})

but in a more general example:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
#"Added Custom" =  Table.AddColumn(#"Changed Type", "Custom", each 
    let search = [Column1] in 
    Text.Combine(
        List.Select(
            Text.Split([Column2],","),
            each Text.Contains(_,search)
        )
    ," ,")
)
in  #"Added Custom"