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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ngadiez
Helper II
Helper II

Pivot the Table

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.

NameItems
AdrianBook, Shoes, Chair
BethPaper, Bottle
CharlesPen, pencil
DewShoes, Sock

 

 

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

@MarcelBeug

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@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...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-chuncz-msft
Community Support
Community Support

@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...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

image.png

 

 

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"

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.

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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