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
nirvana_moksh
Impactful Individual
Impactful Individual

Transpose of table

Hello All,

 

So I have tried pivot and having an index column in the main table and few other approaches but I cannot achieve the following. My main table is like below:

 

IDNameName 2DateDetailRank 1 Rank 2Rank 3CategoryMovement
4050NewarkNew Jersey12.31.2019East Coast -XYZ Street245289300StateNE
4051NewarkNew Jersey12.31.2020East Coast -XYZ Street245289300State

NE

 

 

 

And I want to display the above as is, but also show a transposed version on the bottom (transpose in M did not help):

 

ID40504051
NameNewarkNewark
Name 2New JerseyNew Jersey
Date12.31.201912.31.2020
DetailEast Coast -XYZ StreetEast Coast -XYZ Street
Rank 1 245245
Rank 2289289
Rank 3300300
CategoryStateState
MovementNENE

 

 

In the end, I want the above to only display the transposed value of one selected row, so if someone clicks on row one it basically should show the transpose of that (Column Name's as rows and corresponding values and as the next value column).

 

 

Thank You

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Name 2", type text}, {"Date", type text}, {"Detail", type text}, {"Rank 1 ", Int64.Type}, {"Rank 2", Int64.Type}, {"Rank 3", Int64.Type}, {"Category", type text}, {"Movement", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    Partition = Table.Group(#"Unpivoted Other Columns", {"Index"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Attribute", "Value", "Index1"}, {"Attribute", "Value", "Index1"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN")[Index]), "Index", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index1"})
in
    #"Removed Columns"

The only thing i cannot get to work here is the order of the items in the attribute column to match your order.

Hope this helps.

Untitled.png


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

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Name 2", type text}, {"Date", type text}, {"Detail", type text}, {"Rank 1 ", Int64.Type}, {"Rank 2", Int64.Type}, {"Rank 3", Int64.Type}, {"Category", type text}, {"Movement", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    Partition = Table.Group(#"Unpivoted Other Columns", {"Index"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Attribute", "Value", "Index1"}, {"Attribute", "Value", "Index1"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN")[Index]), "Index", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index1"})
in
    #"Removed Columns"

The only thing i cannot get to work here is the order of the items in the attribute column to match your order.

Hope this helps.

Untitled.png


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

@nirvana_moksh hope attached will do the job.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k - Thanks a lot for taking a stab at this! I did the same initially, and instead of joining to the ID column, in the main table I defined an Index Column which I utilized instead to connect to the other table wherein I did the unpivoting, but this is not working as expected on my end with my entire data set. Also worth mentioning my ID is not a distinct column and each ID might have multiple rows which is the reason I defined the index column. Any other suggestions if you may have?

@nirvana_moksh using index column make sense to bring unique id. So what exactly is not working on your dataset?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k  - Using the index column for the relationship for some reason does not filter the secondary table correctly (clicking on a row on the main table shows up the "first value" of some other row). Just for the sake of it, I used the ID column and that worked, which I think is odd. In your opinion is the usage of an


@parry2k wrote:

@nirvana_mokshusing index column make sense to bring unique id. So what exactly is not working on your dataset?



index column as a unique identifier a good approach?  Because I do use the same logic for other solutions for dynamic axis change. When I check the index column id for one row on the main table and the same for the second it matches, but the value output is not correct for some reason

 

Thank You

@nirvana_moksh i used index it worked as expected. Not sure why in your case it wouldn't work. Does it not work for any of the header level record or works for few and not for others. Just trying to narrow down the issue here although there is no logical explanation.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

 @parry2k - Sadly, doesn't work for anything at all. Like for row 1 see the index is 1 and after that when I see what is the output from the second table and the index for 1 shows up a value for say index 2987. Still trying to figure what can wrong in this process.

@nirvana_moksh here is updated version with index, see if it helps you to find the issue at your end.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k  - thanks a lot for all the assistance! I will keep you posted on this.

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.