Forum Discussion
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?
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 .
3> The colums should split by status with date as a value as shown below .
4>You can then rename columns and perfrom the required date operations .
Hope this helps
Regards,
Mitsu
8 Replies
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 .
3> The colums should split by status with date as a value as shown below .
4>You can then rename columns and perfrom the required date operations .
Hope this helps
Regards,
Mitsu
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
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
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?
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
