Forum Discussion
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 want is to extract matching result (Table) to a single text string.
For example, table1:
ID name
1 mark
2 jack
3 robert
Table2:
ID profession phone
1 health
1 cleaning
2 manager
after join by using ID
ID name joined
1 mark health,cleaning
2 jack manager
3 robert null
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
1 Reply
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