cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Iamnvt
Responsive Resident
Responsive Resident

Merge by Text Contain Any - Multiple values in a row

@Nolock : this is the more general scenarios:

Table 1 have multiple values, separated by Comma, or blank, or any...

Column1
A, B, C
B, D
E, F

 

Table 2: have many result values:

 

WordValue
A12
E22
D10
B5

 

Expected Merged result:

 

Column1Merged withResult
A, B, CA12
A, B, CB5
B, DB5
B, DD10
E, FE22
1 ACCEPTED SOLUTION
Nolock
Memorable Member
Memorable Member

(this post is a continuation of the post https://community.powerbi.com/t5/Power-Query/Merge-by-Text-Contain-Any/m-p/744975#M24766 where the initial requirements have been changed and it was recommended to open a new post).

 

Hi @Iamnvt,

I've prepared 2 tables and a PowerQuery query which combine these two tables.

 

TableWordValue with some sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0UorViVZyBTKNIEwXkKgBmOkEZJqCWQpAllJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Word = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Word", type text}, {"Value", Int64.Type}})
in
    #"Changed Type"

The solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRRcNJRcFaK1YlWAjJcwAxXHQU3pdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    // split text in Column1 into a list
    SplitList = Table.AddColumn(Source, "SplitList", each Text.Split([Column1], ", ")),
    // look up for every word in the table TableWordValue and get a record Word;Value back
    Lookup = Table.AddColumn(
        SplitList, 
        "Lookup", 
        (row) => 
            List.Select(
                List.Transform(row[SplitList], (listItem) => Table.SelectRows(TableWordValue, each [Word] = listItem){0}?),
                each _ <> null
            )
    ),
    // expand lists into rows
    ExpandList = Table.ExpandListColumn(Lookup, "Lookup"),
    // expand record into word and value
    ExpandResultRecord = Table.ExpandRecordColumn(ExpandList, "Lookup", {"Word", "Value"})
in
    ExpandResultRecord

Capture.PNG

View solution in original post

2 REPLIES 2
Nolock
Memorable Member
Memorable Member

(this post is a continuation of the post https://community.powerbi.com/t5/Power-Query/Merge-by-Text-Contain-Any/m-p/744975#M24766 where the initial requirements have been changed and it was recommended to open a new post).

 

Hi @Iamnvt,

I've prepared 2 tables and a PowerQuery query which combine these two tables.

 

TableWordValue with some sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0UorViVZyBTKNIEwXkKgBmOkEZJqCWQpAllJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Word = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Word", type text}, {"Value", Int64.Type}})
in
    #"Changed Type"

The solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRRcNJRcFaK1YlWAjJcwAxXHQU3pdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    // split text in Column1 into a list
    SplitList = Table.AddColumn(Source, "SplitList", each Text.Split([Column1], ", ")),
    // look up for every word in the table TableWordValue and get a record Word;Value back
    Lookup = Table.AddColumn(
        SplitList, 
        "Lookup", 
        (row) => 
            List.Select(
                List.Transform(row[SplitList], (listItem) => Table.SelectRows(TableWordValue, each [Word] = listItem){0}?),
                each _ <> null
            )
    ),
    // expand lists into rows
    ExpandList = Table.ExpandListColumn(Lookup, "Lookup"),
    // expand record into word and value
    ExpandResultRecord = Table.ExpandRecordColumn(ExpandList, "Lookup", {"Word", "Value"})
in
    ExpandResultRecord

Capture.PNG

View solution in original post

Iamnvt
Responsive Resident
Responsive Resident

Truly mastery! Thank you very much.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors