Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Check out the July 2025 Power BI update to learn about new features.