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?
Solved! Go to Solution.
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
before expanding the table, you could just use Table.AddColumn((PriorStepOrTableName, "Name1", each [TableColumnName][Name]{0})
Repeat this process but make the new column name "Name2" and replace the {0} with {1}.
--Nate
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
Thank you for your help! Could you tell me where I should change this code to use it for other form of tables?
You need to use code starting Grouped Rows and replace source with the step where you had generated in second picture.