Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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.
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
At the very least, share some data and show the expected result.
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.
This is how it looks with just question and response, which turns out correctly.
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:
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.
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?
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.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |