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
HunterAlpert
Helper I
Helper I

Unpivoting Columns drawing row consistency issues

Hello,

 

I was hoping receive some assistance with the following issue:

 

I am bringing in data from an SP list to visualize, and my goal is to unpivot all question columns into 1 column for the full question, and 1 column for the response, and all score columns into 1 "Points per question" column. When I unpviot the questions Into one column, I correctly receive the full question and the corresponding responses. However when I unpivot the scores my rows do not align, and instead I get random scores. So its look like this: "Question, Response, Random point Value." when it should be: "Question, Response, Corresponding point value" in the rows. 

 

I am unpivoting my columns incorrectly somewhere?

1 ACCEPTED SOLUTION

@HunterAlpert 

 

Can you maybe share a sample file that has sensitive data removed? Here is a sample file I created based on your question, but I can't see enough from the screenshots to know what I am missing and I"m still confused where 'points' come from. See file below signature. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

At the very least, share some data and show the expected result.


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

It is not often you need to unpivot the data twice. You may need to add an index so you can match the rows up later, and unpivot in two separate tables.

Can you share the sample SharePoint data column headings and describe which ones need to end up where (basically your input data and desired result). Then we can be more specific with answers.

Thanks!

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

That is the weirdest part, usually the rows match up with my index, and I never have this issue. However, the rows in the index column just keep duplicating and recording every possible value when I unpivot and do not adjust when visualized. 

 

So If I have sorted by ID 1, some rows will show all possible outcomes for ID 1 instead of the correct row value.

Hello,  @AllisonKennedy @Ashish_Mathur All my data being filtered by ID

 

This is how the data looks with all my columns unpivoted, which has commentary, missed opportunities, and points rows incorrect.

HunterAlpert_0-1600528984566.png

 

This is how it looks with just question and response, which turns out correctly. 

 

HunterAlpert_1-1600529063841.png

Every piece of data is a column in SP list: Which means I have to unpivot every different category to get the corresponding columns. Raw Columns below:

HunterAlpert_2-1600529232824.png

HunterAlpert_3-1600529267723.pngHunterAlpert_4-1600529292884.png

 

@HunterAlpert 

 

Can you maybe share a sample file that has sensitive data removed? Here is a sample file I created based on your question, but I can't see enough from the screenshots to know what I am missing and I"m still confused where 'points' come from. See file below signature. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hey @AllisonKennedy This did work. However, I am pulling my data from an SP list, and since I created the conditional column and pivoted the values back to columns, I cannot refresh the data. It gets stuck on "evaluating" any ideas what could be causing this or how to fix it?

 

-Thanks!

@HunterAlpert Are you still having issues with this? Can you share the M code from advanced editor and screenshot of where it gets stuck. How long do you let it evaluate for and how large is the SP list?


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hello, apolgies, I have been very sick. 

 

 

My list is less than 5,000 entries, and I will let it evaulate for 1-2 hours before I give up. 

 

Conditional column=

 

= Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Attribute.2], "Missed Opp") then "Opportunities" else if Text.Contains([Attribute.2], "Point") then "Points" else if Text.Contains([Attribute.2], "Commentary") then "Commentary" else "Response")

 

Pivoted Column = = Table.Pivot(#"Removed Columns4", List.Distinct(#"Removed Columns4"[Custom]), "Custom", "Value")

 

This is where it gets stuck. If I remove the pivoted column (which I need) it causes the issue.

HunterAlpert_0-1606149674346.png

 

 

 

 

 

 

 

Where are you trying to pull points from?

It looks lik you have 1-4 columns for each question? Is the Question Number listed in a separate table somewhere?

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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.