Pivoting date field issue

[gustavo80]

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!

[mahoneypat]

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

[gustavo80]

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

[parry2k · 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!

[gustavo80]

Thanks! I was not awared of that unpivot option, it works perfect,thank u very much!

Regards from Argentina 😉

[parry2k]

@gustavo80  My Pleasure!!

From Canada 🍁🍁