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
Anonymous
Not applicable

Date rows as columns

Hi everyone,

 

I have a table with 2 different date columns: "as of date", "prod. date". It also contains a first column with multiple clients and columns for different types of products, as shown in the screenshot.

 

How can I create a separate table filtering for Type 1,  where the dates in column "as of date" appears as column headers, after the column 3: "prod. dates"  e.g. 10/29/2019 .. 10/30/2019.. 10/31/2019.. having hundreds columns of dates?

 

Thanks,

 

Table 3.png

 

 

 

 

 

 

 

 

 

 

 

 

 
1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
santhoshcheran
Advocate I
Advocate I

For creating pivot tables in powerbi, matrix visual comes in handy.

 

Check the below documentation for more:

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual

Anonymous
Not applicable

Thanks @santhoshcheran . A matrix visual won't work for me since I will do multiple further calculations in new columns, based on the desired table, and it will be connected with others. Any ideas for a separate table?

Ashish_Mathur
Super User
Super User

Hi,

Share your desired Table structure.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur , please find below the link for the table structure.

 

https://drive.google.com/open?id=1BjPfE6Vf2Hp8-MEjQLrNzDHBXu6vU6mn

 

Since I will do multiple calculated columns, the desired table looks something like this, filtering for Product Type 1A. The dates shown as columns, are the ones from "as of date" field.

 

Thanks for checking!

 

output.png

Hi,

You may download my PBI file from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thank you @Ashish_Mathur ! The method provided worked for me!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
JosefPrakljacic
Solution Sage
Solution Sage

Hey @Anonymous ,

 

I hope that Ive understood you right. You can use the matrix visual and have the date column in the columns section. Then you can slice how ever you want.

 

Example.gif

 

If this solution doesnot help you maybe you could provide a more detailed explanation.

 

If this post was helpful may I ask you to mark it as solution and give it a 'thumbs up'? This will also help others

Have a nice day!

BR,
Josef
Graz - Austria

Anonymous
Not applicable

Hi @JosefPrakljacic . Thanks for your reply.

 

I miss to mention that I would need the results in a new table, so I can calculate different columns on it, like averages, multiplications etc. Those calculations from the new table, and others, relate will feed a final matrix visual.

Hey @Anonymous,

 

you need to know that calculated tables are preprocessed changing the columns by a slicer wouldn't have any affect to the calculated table. So your newly generated columns actually need to be measures.

 

BR,

Josef

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.