cancel
Showing results for 
Search instead for 
Did you mean: 
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
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
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
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors
Top Kudoed Authors