Formatting data from multiple variable rows to one single row

[MdJ83]

I'm looking at consolidating mutlipe rows into one row, although the number of rows per items is variable. Looking for some advice for either in power query or dax to perform this.

Might be easier to explain with an example. Current data looks like this

Supplier No.	Name	Contact Type	Contact
222	ABC	Bus	000 000 000
222	ABV	Bus	123 123 123
222	ABC	Fax	000 000 000
222	ABC	Email	@mail.com
333	CDF	Bus	000 000 000
333	CDF	Email	@mail.com
444	XYZ	Email	@mail.com

Want to transform it into this

Supplier No.	Name	Contact Type	Contact	Contact Type	Contact	Contact Type	Contact	Contact Type	Contact
222	ABC	Bus	000 000 000	Bus	123 123 123	Fax	000 000 000	Email	@mail.com
333	CDF	Bus	000 000 000					Email	@mail.com
444	XYZ							Email	@mail.com

[v-yanjiang-msft · Accepted solution]

Hi @MdJ83 ,

According to your description, here's my solution. You can copy-paste the code in a blank query to see the details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIyUtJRcnRyBpJOpcVA0sDAQAGKlWJ1ECrC4CoMjYwVoBhFBcgMt8QKPGaAVLjmJmbmAGkHEK2XnJ8LVmFsbAwUc3Zxw+YOBQwl2A0xMTEBikVERmFXEQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Supplier No." = _t, Name = _t, #"Contact Type" = _t, Contact = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Supplier No.", Int64.Type}, {"Name", type text}, {"Contact Type", type text}, {"Contact", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Supplier No."}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value",each Text.Combine(List.Sort(_), ",")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Name.1", "Name.2"}),
    #"Replaced Value"=Table.ReplaceValue(#"Split Column by Delimiter",each[Contact Type],each if [Contact Type]="Bus,Email" then "Bus,,Email" else if [Contact Type]="Email" then ",,Email" else [Contact Type], Replacer.ReplaceValue,{"Contact Type"} ),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value", "Contact Type", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Contact Type.1", "Contact Type.2", "Contact Type.3","Contact Type.4"}),
    #"Replaced Value2"=Table.ReplaceValue(#"Split Column by Delimiter1",each[Contact],each if [Contact]="000 000 000 ,@mail.com" then "000 000 000 ,,,@mail.com" else if [Contact]="@mail.com" then ",,,@mail.com" else [Contact], Replacer.ReplaceValue,{"Contact"} ),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Replaced Value2", "Contact", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Contact.1", "Contact.2", "Contact.3", "Contact.4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter2",{"Name.2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Supplier No.", "Name.1", "Contact Type.1", "Contact.1", "Contact Type.2", "Contact.2", "Contact Type.4", "Contact.3", "Contact Type.3", "Contact.4"})
in
    #"Reordered Columns"

Get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.