Power Query - Add custom column based on values found in another lookup Query

user_19917150 0
Viewed 90

I like to add a column in MainQuery and fill it up with values found in another LookupQuery based what it being able to find the matching unique value embedded in the text of column1 in my Main Query. if not found just return blank, null. Thank you

Main Query

Column1
...111.
..ABC..
..34C..
...xyz.
yyy.....

Lookup Query

Uniques
34C
ABC
111

Desired Output in Main Query

Column1 New Column
...111.. 111
..ABC... ABC
..34C... 34C
....xyz.
yyy.....
powerbi powerquery
2 Answers

Here's another method using List.Accumulate to loop through the lookup list. Not sure which of the methods will be more efficient.

Note: If there might be more than one lookup result for a given item in column 1, a small change in the List.Accumulate function can accommodate showing them all with a separator

let

//Read in Main Query
    Source = Excel.CurrentWorkbook(){[Name="Main"]}[Content],
    Main = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

//Read in Lookup Query as a list of text items
    Lookup = List.Buffer(
                List.Transform(Excel.CurrentWorkbook(){[Name="Lookup"]}[Content][Uniques], 
                    each Text.From(_))),
     
//add Column
    #"Add Column" = 
        Table.AddColumn(
            Main, "New Column", each 
                List.Accumulate(Lookup, 
                                null, 
                                (state, current)=> 
                                    if Text.Contains([Column1], current) 
                                        then current else state), 
                                        type nullable text)
in
    #"Add Column"

enter image description here

Results
enter image description here

user_28729220

There's a not a built-in join that does this, but you can cross-join followed by a filter. And to Cross Join in PQ you add a custom column to one table containing the full value of the other table, then run Table.ExpandTableColumn.

EG

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjE0NFSK1YlWKk5Jc3RyNrY0BvMSs4uzgDLZxYlZKWnZxibOSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Lookup),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Uniques"}, {"Uniques"}),
    #"Selected Rows" = Table.SelectRows(#"Expanded Custom", each Text.Contains([Column1], [Uniques]))
in
    #"Selected Rows"
user_72977000
Related