Combine multiple rows into one row

botaac — Wed, 28 Jun 2023 15:44:18 GMT

I have the following table

Name	ID1	Item Type	ID2	ID3	USD Amount	ID4	Rank	Contracts	Notes
Bank1			8910
Bank1					100	SH782	400
Bank1	123	Vendor
Bank1				456

Utilizing DAX or Power Query, I need it to be transformed to the following

Name	ID1	Item Type	ID2	ID3	USD Amount	ID4	Rank	Contracts	Notes
Bank1	123	Vendor	8910	456	100	SH782	400

I utilized the SUMMARIZE function and pulled all of those datafields into it, but still get the rows being separated.

Re: Combine multiple rows into one row

Syk — Wed, 28 Jun 2023 18:26:33 GMT

In Power Query I pasted your data into a table and achieved that result with this M code.
The first thing it does is transposes the table so your headers are now rows in column1. Then I replaced all the " " values with nulls and created a custom column to get the first non-null value (the ?? in the code below means to coalesce) This gets the first value from each of those columns and returns it as 1 column which I named Value.
Then I removed columns 2-5 and pivoted the header column (column1) with 'Value' as the values using the "Don't aggregate" under advanced options of the pivot column dialog box.

Hope this helps!

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUfJ0MQSRJam5CiGVBRARIzBpDCRDg10UHHPzS/NKwEImQDIoMS8bSDnn55UUJSaXFAPZfvklqcVKsTrRSk5ASZB5ClBsYWlogMRFxdg0wLChAUhfsIe5BcgxJgYGWHUZGoEcGZaal5JfhNMafFaZmJphVx8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}, {"(blank).8", type text}, {"(blank).9", type text}}), #"Transposed Table" = Table.Transpose(#"Changed Type"), #"Replaced Value" = Table.ReplaceValue(#"Transposed Table"," ",null,Replacer.ReplaceValue,{"Column2", "Column3", "Column4", "Column5"}), #"Added Custom" = Table.AddColumn(#"Replaced Value", "Value", each [Column2] ?? [Column2] ?? [Column3] ?? [Column4] ??[Column5]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column2", "Column3", "Column4", "Column5"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Column1]), "Column1", "Value") in #"Pivoted Column"

topic Re: Combine multiple rows into one row in DAX Commands and Tips

Combine multiple rows into one row

Re: Combine multiple rows into one row