cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Iamnvt Member
Member

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

Accepted Solutions
Super User
Super User

Re: Merge by Text Contain Any - Multiple values in a row

(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

2 REPLIES 2
Super User
Super User

Re: Merge by Text Contain Any - Multiple values in a row

(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

Iamnvt Member
Member

Re: Merge by Text Contain Any - Multiple values in a row

Truly mastery! Thank you very much.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 271 members 2,903 guests
Please welcome our newest community members: