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
Anonymous
Not applicable

Pivot/Unpivot two columns by value

For reference i am only using one column (Cust ID). I feel like this isnt hard but i cant figure out how to get my column data into multiple rows. I have attached an image -- seems pretty straightforward. When i try to pi

 

cesarvaldez_0-1621374379177.png

 

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Partition = Table.Group(Source, {"Zip"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Cust ID", "Index"}, {"Cust ID", "Index"}),
    #"Added Prefix" = Table.TransformColumns(#"Expanded Partition", {{"Index", each "Cust ID" & Text.From(_, "en-IN"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Index]), "Index", "Cust ID")
in
    #"Pivoted Column"

Hope this helps.

Untitled.png


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

@Ashish_Mathur That helped me a lot. I was hoping this would help me with two columns instead of one. Any thoughts? I have a 3 rd column CustName which matches w a CustID. Is it possible to end up w columns such as: Zip(unique), CustName1, CustID1, CustName2, CustID2, CustName3, CustID3, etc.

You are welcome.  I can get CustName1 ad CustID1 in one cell/column - not in different columns.  May be someone else can help.


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

@themistoklis Thanks for the answer but i was hoping to achieve this table raw In Power Query before loading it to PBI

themistoklis
Community Champion
Community Champion

@Anonymous 

 

Create a new column e.g. Customer No and add the following formula:

Customer No = "Cust " &
RANKX (
FILTER (Sheet1, Sheet1[Zip]= EARLIER(Sheet1[Zip]) ), -- Grouping Column
Sheet1[Cust ID], -- Sorting Column
, ASC
)
 
Then Create a matrix visual and add the data.
 
I removed totals from the matrix and for values field (Cust ID) I used the MAX aggregation method.
 
See attached file

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.