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
gustavo80
Helper III
Helper III

Pivoting date field issue

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.

 

example.jpg

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!

 

1 ACCEPTED 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.

View solution in original post

5 REPLIES 5
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


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

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.