Pivot the Table

[ngadiez]

Hi All

I want to pivot the table.

There is a lot of tutorials to unpivot the table in the power query. 

However, I want to get the pivot separated by comma like this from the unpivot table.

Name	Items
Adrian	Book, Shoes, Chair
Beth	Paper, Bottle
Charles	Pen, pencil
Dew	Shoes, Sock

Is there any way to do this in M language? I don't want to do it in DAX.

@MarcelBeug

[v-chuncz-msft · Accepted solution]

@ngadiez,

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/Agroup-and-concatenate-column-with-a-common-value/td-p/2118...

[Anonymous]

I'm assuming you're after something like this:

Split on each occurance of comma, and then unpivot the broken out items (select all but the Name and unpivot). I took the liberty of trimming and making the casing consistent:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcxBCsAgDETRq4Ssc4naHqDgUlyIDSiKkSj0+rWl23nDdw63S3NoSGhECoFNwoNgTyErenJoeKalZ+isBEbmrPzBumjl8Ro3gs4t5vrJwfda/5KVWND7Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Items = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Items", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Items", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Items.1", "Items.2", "Items.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Items.1", type text}, {"Items.2", type text}, {"Items.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Name"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Trimmed Text",{{"Value", Text.Proper, type text}})
in
    #"Capitalized Each Word"

[ngadiez]

Hi Both,

What I want to achieve is the pivot table, not unpivot table.

So instead of each row consists only 1 item, i want to consolidate them into column with comma separated values.

[Ashish_Mathur]

Hi,

I am confused.  Show your actual data and also your expected result.

[Ashish_Mathur]

Hi,

In the Query Editor window (Edit Query), right click on the heading of the second column and under Split columns > Delimiter > Comma, go to Advanced and select rows there.  Click on OK.  Now each item will appear in its own cell.  You can create a Pivot Table.