Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone! I'm pretty sure that my problem could be solved through pivot or unpivot feature, but I encountered a problem that I wasn't able to fix.
My dataset looks like:
| installs | signups |
| players | sesions |
30/5/2020 | 10 | 20 |
| 30 | 40 |
29/5/2020 | 20 | 33 |
| 44 | 22 |
28/5/2020 | 20 | 11 |
| 33 | 5 |
27/5/2020 | 15 | 10 |
| 22 | 6 |
26/5/2020 | 16 | 6 |
| 8 | 7 |
I need to show it transposed, like:
| 30/5/2020 | 29/5/2020 | 28/5/2020 | 27/5/2020 |
installs | 10 | 20 | 20 | 15 |
signups | 20 | 33 | .. | . |
players | … | .. | . | . |
sesions | .. | .. | .. | 6 |
Using pivot feature, seems to be ok.
But the problem, as the date columns now (due to pivot action) are metrics, I need to select one by one to include it in the table. But as my query datasource every day will return new days ( it recover last 4 days metrcs ) , the "new date" included every day, appears not checked ( as you can see 30/05/2020), so doesn't appear in the table.
Is there any solution to do it ? I mean, I need to show always the days returned into my datasource, with no manually intervention.
My .pbix attached, with the original table ( a sample ) and the pivoted table as other
source: https://drive.google.com/file/d/19VX0qKjk25MgowRCIxjBf6MWZ9na_v6Z/view?usp=sharing
Thanks in advance,
Regards!
Solved! Go to Solution.
@gustavo80 see attached, you just unpivot and then view in a matrix, it is the easiest and most scalable solution and follows the best practice from a data modeling perspective.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
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.
Why are you pivoting out the dates? In almost all cases, that makes your model harder to analyze/visualize. If you really need to do it, you can likely use Table.ColumnNames() to dynamically generate a list of your date column names to use in whatever function currently has a list of static column names.
But you should have a really good reason for doing the pivot. Your starting table structure is good (you could even consider unpivotting it further), and you can make your desired visual showing 4 dates from that kind of structure.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks for the quick response! I obviusly prefer not to pivot, it was just a try that I made to show the information as I need ( dates in columns, measures in rows ).
How should I do ? I tried with a table and with a matrix but I coudn't show the data as I need :S
@gustavo80 see attached, you just unpivot and then view in a matrix, it is the easiest and most scalable solution and follows the best practice from a data modeling perspective.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
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.
Thanks! I was not awared of that unpivot option, it works perfect,thank u very much!
Regards from Argentina 😉
@gustavo80 My Pleasure!!
From Canada 🍁🍁
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |