Forum Discussion
Solved
Is there a way to expand left outer join result table having many rows to new columns?
Hi, I'm an excel newbie.
I have two tables like this:
And default result for inner outer join with postId will be:
But I want to make a table like this:
Is there a way to achieve this with excel?
Use this code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSrILy4B0WmZOamGSrE6mKJGYFEjqCiITszLL8lILQKLG0PFQbRSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [postid = _t, title = _t, name = _t]), #"Grouped Rows" = Table.Group(Source, {"postid", "title"}, {{"name", each Text.Combine(_[name],", ")}}), Result = Table.SplitColumn(#"Grouped Rows", "name", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), List.Transform({1..List.Max(Table.AddColumn(#"Grouped Rows", "TempStep", each List.Count(Text.Split([name],", ")))[TempStep])},each "name." & Number.ToText(_))) in Result
4 Replies
- Vijay_A_Verma
Most Valuable Professional
Use this code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSrILy4B0WmZOamGSrE6mKJGYFEjqCiITszLL8lILQKLG0PFQbRSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [postid = _t, title = _t, name = _t]), #"Grouped Rows" = Table.Group(Source, {"postid", "title"}, {{"name", each Text.Combine(_[name],", ")}}), Result = Table.SplitColumn(#"Grouped Rows", "name", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), List.Transform({1..List.Max(Table.AddColumn(#"Grouped Rows", "TempStep", each List.Count(Text.Split([name],", ")))[TempStep])},each "name." & Number.ToText(_))) in ResultThank you for your help! Could you tell me where I should change this code to use it for other form of tables?
- Vijay_A_Verma
You need to use code starting Grouped Rows and replace source with the step where you had generated in second picture.
