Merge two rows into a single row

[brdrok]

Hi,

I hope that someone can help me with the following issue.  I am trying to merge two or more rows into a single row based on a key column.  Hopefully the image below can show it better than I can explain it:

In the picture above, I would like to create one single row for AccountKey 16410 and have the InvestorTotaling be merged into a single row.

Thank you

[Aburar_123]

Hi @brdrok ,

You can try it with Concatenatex() function as below

[v-tejrama]

Hi @brdrok ,

After performing group by → accountkey with the operation set to all rows, you'll see a column displaying “table” for each row.
Don’t expand it using the arrows; instead, click the gear icon next to the grouped step in applied steps.
In the dialog, select only the investortotaling column and remove the others.
The grouped column will now become a list.
Right-click the investortotaling column header and choose extract value.
Pick your preferred delimiter (such as | or ,) to combine all investor codes into a single text string for each account key.

Thank you,
Tejaswi

[v-tejrama]

Hi @brdrok ,

Thank you @mdaatifraza5556 and @FBergamaschi  for the response provided!  

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone. 

Thank you for your understanding

[v-tejrama]

Hi @brdrok ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[v-tejrama]

Hi @brdrok ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you,

Tejaswi.

[brdrok]

Hi @FBergamaschi ,

trying to attach an excel, csv, or pbix files but none will take.  Do you have any suggestions how to get the data to you?

[Ashish_Mathur · Accepted solution]

Hi,

Upload the file to Google Drive/One Drive and share the download link here.

[mdaatifraza5556]

Hi @brdrok 

As per my understanding of your requirement, I have tried with some demo data

Can you please try the below M code

Just copy the M-code and use from #"Split Column by Delimiter" becasuse you already have your table so from there u use the M- code

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUqsSVKK1YlWcgKyk2tSwGyQeGpNGlw8vSYDzHYGsjNqMpViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Investor = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Investor", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Investor", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Investor"),
GroupedRows = Table.Group(#"Split Column by Delimiter", {"Key"}, {
{"investors", each _, type table [Key=nullable text, Investor=nullable text]}
}),
AddedCustom = Table.AddColumn(GroupedRows, "Investor", each Text.Combine([investors][Investor], "|")),
FinalTable = Table.SelectColumns(AddedCustom, {"Key", "Investor"})
in
FinalTable

Data I used

Result

If this answers your questions, kindly accept it as a solution and give kudos.

[FBergamaschi]

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here