Forum Discussion

WillianLopes's avatar
WillianLopes
New Member
3 years ago
Solved

How to merge 2 tables in PowerQuery using substrings?

I have a sold items table. In this list, in addition to the name of the product, there are other expressions required by law - all on a single line.   In the second table I have the name of the pro...
Need Help
Tutorial Requests
  • WillianLopes's avatar
    WillianLopes
    3 years ago

    On another site I was given a great solution that I will share here to document.

     

    First add a custom column:

    Table.SelectRows(Table2, (x)=> Text.Contains ([Sold items], x[Products], Comparer.OrdinalIgnoreCase) )

     

    Now, expand the column.

    The code will be:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sold items", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(Table2, (x)=> Text.Contains ([Sold items], x[Products], Comparer.OrdinalIgnoreCase) )),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Products"}, {"Products"})
    in
    #"Expanded Custom"

JW_van_Holst's avatar
JW_van_Holst
Resolver IV
3 years ago

Here you are:

 

let
    Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckrMA8JipVidaCXHgoKcVDDLqzQzGcIKSM1LzsxRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Products = _t]),
    listOfProd = Source2[Products],
    Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bYzLCYBADAVbeexZi9AuZPEQNYqQTRajqN37u8och5kYQ8MitqMjvXGU6E19Sww309AWMVTo7ICN8EQioJyF/TW1bAzSAec3yaz9LHBe/0JbSKenbC8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sold Items" = _t]),
    #"Added Custom" = Table.AddColumn(Source1, "Product", (x)=>  
        let
            attempt =  try List.Select(listOfProd, each Text.Contains(x[Sold Items], _, Comparer.OrdinalIgnoreCase) ){0},
            result =    if attempt[HasError] then
                            null
                        else
                            attempt[Value]
        in
            result
    )
in
    #"Added Custom"