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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
T01JDUHART
Regular Visitor

Trouble merging rows of data

Hi community. I have the following table:

 

Project NameShareholder% Share
ADNOCBP10%
ADNOCTotalEnergies5%
ADNOCADNOC70%
ADNOCMitsui15%
AGRIAGRI100%
AbadiINPEX65%
AbadiShell35%

 

As you can see I have several shareholders for each project which translates to several rows for 1 project. I would like the output table to be something like this with just 1 row per project:

 

Project NameShareholders
ADNOCBP 10%, TotalEnergies 5%, ADNOC 70%, Mitsui 15%
AGRIAGRI 100%
AbadiINPEX 65%, Shell 35%

 

I am having a hard time finding a solution in Power Query.

Thanks!

1 ACCEPTED SOLUTION
sevenhills
Super User
Super User

Try this:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnTx83dW0lFyCgAShgaqSrE6CMGQ/JLEHNe81KL0zNRiIN8UVRpGm6Np880sKS7NBJkH0+Ae5AlSD6EMDWDqkxJTQMo8/QJcI4C0mSmqeHBGak4OkDYGiccCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, Shareholder = _t, #"% Share" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Shareholder", type text}, {"% Share", Percentage.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"% Share", type text}}, "en-US"),{"Shareholder", "% Share"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Added Suffix" = Table.TransformColumns(#"Merged Columns", {{"Merged", each _ & "%", type text}}),
    #"Grouped Rows" = Table.Group(#"Added Suffix", {"Project Name"}, {{"Shareholders", each Text.Combine([Merged], ", "), type text}})
in
    #"Grouped Rows"

 

IP:

sevenhills_0-1685574008281.png

 

 

 

OP:

sevenhills_2-1685574133698.png

 

Hope it helps!

View solution in original post

2 REPLIES 2
sevenhills
Super User
Super User

Try this:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnTx83dW0lFyCgAShgaqSrE6CMGQ/JLEHNe81KL0zNRiIN8UVRpGm6Np880sKS7NBJkH0+Ae5AlSD6EMDWDqkxJTQMo8/QJcI4C0mSmqeHBGak4OkDYGiccCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, Shareholder = _t, #"% Share" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Shareholder", type text}, {"% Share", Percentage.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"% Share", type text}}, "en-US"),{"Shareholder", "% Share"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Added Suffix" = Table.TransformColumns(#"Merged Columns", {{"Merged", each _ & "%", type text}}),
    #"Grouped Rows" = Table.Group(#"Added Suffix", {"Project Name"}, {{"Shareholders", each Text.Combine([Merged], ", "), type text}})
in
    #"Grouped Rows"

 

IP:

sevenhills_0-1685574008281.png

 

 

 

OP:

sevenhills_2-1685574133698.png

 

Hope it helps!

vicky_
Super User
Super User

Does it have to be in Power Query? In DAX, you can create a measure with the CONCATENATEX() function to join all the shareholder names with the same project name.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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