cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Accepted Solutions

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

 


______________


Has this post solved your problem? Please mark it as a 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.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

9 REPLIES 9
Super User II
Super User II

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!

 


______________


Has this post solved your problem? Please mark it as a 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.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Super User III
Super User III

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/

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

 

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.

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?

 


______________


Has this post solved your problem? Please mark it as a 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.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

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

 


______________


Has this post solved your problem? Please mark it as a 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.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

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?

 


______________


Has this post solved your problem? Please mark it as a 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.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


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

 

 

 

 

 

 

 

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors