Forum Discussion

Vampirtc's avatar
Vampirtc
Frequent Visitor
5 years ago
Solved

Extract records from Table to Text

The problem is the following: I need to match two tables, I'm using Left Outer Join. Second table can have 0,1,2 or more matches. If I use expand after matching the columns are duplicated. What I...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Vampirtc , you are only one step away from your goal,

    let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcpNLMpWitWJVjICcrISkyEcYyCnKD8ptahEKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, name = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcpITcwpyVCK1YFwk3NSE/My89LBAkZAgdzEvMT01CKl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Profession = _t]),
    #"Left Outer join" = Table.NestedJoin(Table1, "ID", Table2, "ID", "joined", JoinKind.LeftOuter),
    Concatenation = Table.TransformColumns(#"Left Outer join", {"joined", each Text.Combine([Profession], ", ")})
in
    Concatenation

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

Vampirtc , you are only one step away from your goal,

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcpNLMpWitWJVjICcrISkyEcYyCnKD8ptahEKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, name = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcpITcwpyVCK1YFwk3NSE/My89LBAkZAgdzEvMT01CKl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Profession = _t]),
    #"Left Outer join" = Table.NestedJoin(Table1, "ID", Table2, "ID", "joined", JoinKind.LeftOuter),
    Concatenation = Table.TransformColumns(#"Left Outer join", {"joined", each Text.Combine([Profession], ", ")})
in
    Concatenation