Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
kangdyu
New Member

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:

kangdyu_0-1680081779726.png

And default result for inner outer join with postId will be:

kangdyu_1-1680081860748.png

 

But I want to make a table like this:

kangdyu_3-1680082111296.png

 

Is there a way to achieve this with excel?

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

Vijay_A_Verma
Super User
Super User

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors