Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jvi_bobdebouwer
Frequent Visitor

Transform data: rows to columns based on values in rows

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 IDAction codeAction code descriptionDateTimeEntry created by
44300002Arrival time09-01-201814:10:00MECHANIC1
44300005Functionally repaired time09-01-201814:45:00MECHANIC1
44300007Departure time09-01-201814:45:00MECHANIC1
44300002Arrival time23-02-201813:44:00MECHANIC1
44300006Definitive repaired time23-02-201813:52:00MECHANIC1
44300007Departure time23-02-201813:52:00MECHANIC1
44320002Arrival time03-01-201812:34:00MECHANIC1
44320005Functionally repaired time03-01-201812:43:00MECHANIC1
44320007Departure time03-01-201812:43:00MECHANIC1
44320001Notification time03-01-201813:31:00MECHANIC2
44320005Functionally repaired time04-01-201817:22:00MECHANIC2

 

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 IDEntry created byArrival date/timeFunctionally repaired date/timeDefinitive repaired date/timeRepair time
4430MECHANIC19-01-18 14:109-01-18 14:45 0:35:00
4430MECHANIC123-02-18 13:44 23-02-18 13:520:08:00
4432MECHANIC13-01-18 12:343-01-18 12:43 0:09:00
4432MECHANIC2 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

1 ACCEPTED SOLUTION
jvi_bobdebouwer
Frequent Visitor

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!

 

 

scr_PBI_NeedHelp_Intermediate00.png

 

scr_PBI_NeedHelp_Intermediate01.png

 

scr_PBI_NeedHelp_Intermediate02.png

 

scr_PBI_NeedHelp_Intermediate03.png

 

View solution in original post

3 REPLIES 3
jvi_bobdebouwer
Frequent Visitor

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!

 

 

scr_PBI_NeedHelp_Intermediate00.png

 

scr_PBI_NeedHelp_Intermediate01.png

 

scr_PBI_NeedHelp_Intermediate02.png

 

scr_PBI_NeedHelp_Intermediate03.png

 

Mepoo127
Frequent Visitor

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:

Community1.PNG

 

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:

Community2.PNG

You can add another condition to check Blank Arrival time.

 

Hope it helps!!

 

 

 

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? 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.