cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
danferboc Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

Re: Date rows as columns

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
JosefPrakljacic Established Member
Established Member

Re: Date rows as columns

Hey @danferboc ,

 

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

danferboc Regular Visitor
Regular Visitor

Re: Date rows as columns

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.

JosefPrakljacic Established Member
Established Member

Re: Date rows as columns

Hey @danferboc,

 

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

Super User
Super User

Re: Date rows as columns

Hi,

Share your desired Table structure.


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

Re: Date rows as columns

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

santhoshcheran Frequent Visitor
Frequent Visitor

Re: Date rows as columns

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

danferboc Regular Visitor
Regular Visitor

Re: Date rows as columns

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?

Super User
Super User

Re: Date rows as columns

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

danferboc Regular Visitor
Regular Visitor

Re: Date rows as columns

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,043)