Transform data: rows to columns based on values in rows

[jvi_bobdebouwer]

Dear all,

I've tried searching the community for a solution, but was unable to find one, mainly because I find it hard to word my problem. Maybe its best I dive into it with using the following example.

The data we receive is formatted as follows:

Notification ID	Action code	Action code description	Date	Time	Entry created by
4430	0002	Arrival time	09-01-2018	14:10:00	MECHANIC1
4430	0005	Functionally repaired time	09-01-2018	14:45:00	MECHANIC1
4430	0007	Departure time	09-01-2018	14:45:00	MECHANIC1
4430	0002	Arrival time	23-02-2018	13:44:00	MECHANIC1
4430	0006	Definitive repaired time	23-02-2018	13:52:00	MECHANIC1
4430	0007	Departure time	23-02-2018	13:52:00	MECHANIC1
4432	0002	Arrival time	03-01-2018	12:34:00	MECHANIC1
4432	0005	Functionally repaired time	03-01-2018	12:43:00	MECHANIC1
4432	0007	Departure time	03-01-2018	12:43:00	MECHANIC1
4432	0001	Notification time	03-01-2018	13:31:00	MECHANIC2
4432	0005	Functionally repaired time	04-01-2018	17:22:00	MECHANIC2

What I want is to determine the repair time the mechanic needed to I can plot that in a graph. The repair time is either Functionally repaired time - Arrival time or Definitive repaired time - Arrival time.

In order to do that I wanted to restructure my data as follows:

Notification ID	Entry created by	Arrival date/time	Functionally repaired date/time	Definitive repaired date/time	Repair time
4430	MECHANIC1	9-01-18 14:10	9-01-18 14:45		0:35:00
4430	MECHANIC1	23-02-18 13:44		23-02-18 13:52	0:08:00
4432	MECHANIC1	3-01-18 12:34	3-01-18 12:43		0:09:00
4432	MECHANIC2		4-01-18 17:22		invalid entry - check data

And this is where I hit rock bottom.

How do I transform my data from rows into columns? That is, how do I get/select the arrival time in my source table and get it on the correct row in my 'destination' table?

Any help, clue, partial solution and/or hint whatsoever is appreciated.

Click here for the source data and the result I hope to get (no calculations, just manually manipulated data).

Thanks in advance!

Jeroen

[jvi_bobdebouwer · Accepted solution]

I've been searching the community/forum for a solution and came up with one. It's quite lengthy, so I'll stick to the highlights.

I am posting it for others to enjoy, but also to check if somebody else may have an idea on how to do this more easily so.

Step 1 - Load the source data
Step 2 - Create 3 new tables using 'Reference' (so I only have to add certain columns once, see step 5)
Step 3 - Filter the newly created tables on action code, i.e. the table 'SourceData_ArrivalTime' has a filter on column 'Action code description'='Arrival Time'.
Step 4 - Create a 4th new table (also using 'Reference') to act as a Key or Main table in which all 'resulting' times will be stored
Step 5 - I needed a unique key to combine the Key table and the Time tables. The unique combination I chose is 'Notification ID - Date - Entry Created By'. Here comes the beauty of referencing tables: I created a new column in the SourceData table named ConcatKey and it showed up in all tables! 🙂
Step 6 - Use the Merge Queries function in the query editor to add the different Time tables to the Key table, link using the ConcatKey.
Step 7 - Expand the merged queries, and make it show only the applicable time (e.g. SourceData_ArrivalTime)
Step 8 - Add a column to calculate the time difference
Step 9 - Be smug and happy that I found above solution 🙂

Please find below some screenshots which may help you reproduce and/or clarify what I tried to explain in above steps.

Again, if anybody else knows how to do this more quickly/smartly, please let me know!

[Mepoo127]

Hi,

Please select Edit Query and from Query Editor, you perform the following steps:

- Add a custom column to combine your Date and Time columns: [Date] & [Time]

- Change the type of the colum to 'Date & Time'

- Select the Action code description column to and click Pivot Column based on the 'Date & Time' value and please remember to select 'Don't Aggregate'

- Sort Date Column in Ascending order

- Select the Arrival Time column to Fill up the data.

You will end up with something like this:

After you Close and Apply, you can add column with something like this:

Repair Time = IF(AND(Test[Arrival time] <> BLANK(),Test[Functionally repaired time] <> BLANK()), Test[Functionally repaired time] - Test[Arrival time], IF(AND(Test[Arrival time] <> BLANK(),Test[Definitive repaired time] <> BLANK()), Test[Definitive repaired time] - Test[Arrival time], BLANK()))

And please remember to change the data to Time and you will get the results:

You can add another condition to check Blank Arrival time.

Hope it helps!!

[jvi_bobdebouwer]

Hi Mepoo127,

Thanks for the quick and elaborate reply. It does give me some good insights and things to try out.

For me the drawback using the Pivot option is that quite some extra rows are introduced. An example of that is row 3 (using your table in picture 3). It only has an Arrival time, nothing else. 

Using your fill option is totally new to me. I liked and will dive into it more. When I was trying to rebuild your anwser I ran into the issue that the column 'Entry created by' wasn't sorted anymore. So MECHANIC1 and MECHANIC2 were randomly placed. Using fill in that case results that MECHANIC2 also go an Arrival time. It was relatively easy to correct, but in a large dataset I dont think I would have spotted it. So that could be a drawback to using the Fill option. 

In Excel itself I would be using a VLOOKUP construction in column 'Arrival time' based on a lookup of the order number and column 'Actioncode'. This way I would find only the correct records and fill up the 'Arrival time' (and other) columns. 

Do you (or anybody else 🙂 ) know what the VLOOKUP 'way' in Power BI is?