cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fer_vd
Frequent Visitor

Pivot table with multple dates on the same id

Hi, I'm still new to power BI and not too familiar with DAX. The situation is this:

I have a table which contains all the changes to the state of an order. The same order can have multple changes on other dates. Every change is saved on it's own record with the ID, ChangeCode and Date (and unimportant things).

 

The thing I want to do is: make a graph which shows the time between the different ChangeCodes. I tried to pivot the table but did not get it to work.

Could anyone help me out?

 

printscreen.png

 

 

1 ACCEPTED SOLUTION
mitsu
Resolver IV
Resolver IV

Following is what you can try  in the import data mode 

 

 

1> Go to Query Editor .

2> Pivot Column Status with value as Date and  in the advanced option select do not Aggregate .

PivotOptions.JPG

 

 

 

 

3> The colums should split by status with date as a value as shown below .

Capture.JPG

 

4>You can then rename columns and perfrom the required date operations .

 

 

Hope this helps 

 

 

 

Regards,

Mitsu

View solution in original post

8 REPLIES 8
mitsu
Resolver IV
Resolver IV

Following is what you can try  in the import data mode 

 

 

1> Go to Query Editor .

2> Pivot Column Status with value as Date and  in the advanced option select do not Aggregate .

PivotOptions.JPG

 

 

 

 

3> The colums should split by status with date as a value as shown below .

Capture.JPG

 

4>You can then rename columns and perfrom the required date operations .

 

 

Hope this helps 

 

 

 

Regards,

Mitsu

View solution in original post

fer_vd
Frequent Visitor

Thank you Mitsu! This works. I've been struggling with this for too long and the answer is pretty easy once you know it.. So thanks a lot!

I'll tag the topic as solved :).

 

Fer

 

CheenuSing
Community Champion
Community Champion

@fer_vd

 

1. Could you please let us know how you want the output to look like.

 

2.  Are you sing DirectQuery or ImportData mode for building your data model.

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi thanks for your respond.

 

Im trying to get all ordernumbers combined on 1 row with all the states that order has been in. The amount of state can differ though. Most orders have 4 states, but some 2 3 or 5.

I dont know if this is the easiest way to make a graph of datedifferences, possibly there are better ways?

 

 

Printscreen2.png

Hi fer_vd,

 

If you would like to achieve this (Combine values in 1 row) under Power BI Query Editor, then I am afraid this is not available.

Power BI is a column based tool, which is different from Excel (cell based).

By the way, you may take a look at the Matrix Visual, see if this type of Visual would meet your requirements regarding the data display:

How to Use Tables and Matrixes

Another thoughts is we could take use of Power BI publisher For Excel. Use Excel function to create Pivot tables which would meet your requirements, then publish it from Excel directly to Power Bi service:

Power BI publisher for Excel

 

Please let me know if I have any further misunderstandings about your situation.

Regards

Hi Michael,

 

I prefer to use only power BI to accomplish this task. The date difference between states was one of the questions asked to vizualize in power BI. The information itself comes from a SQL database.

As little background information: For an internship I'm setting up a couple of example reports.

CheenuSing
Community Champion
Community Champion

@fer_vd

 

Could you please calrify

 

1. What will be maximum states in your data model.

2. What are states and the order. - OIO->ORF->OVL->OVS

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

The order can be any purchase made at a webshop. The possible states the order can have are in the table below. The number is the order in which the states happen. Some states are optional (for example deleted or blocked and are thus empty)

OIO

arrived order                                          

1

OOB

Blocked order                                          

 

ORF

Order printed                                           

3

ORR

Order reserved                                    

2

OVA

canceled order                                         

 

OVD

Order deleted                                     

 

OVL

Order label create                        

5

OVS

Order fully scanned                                

4

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors