Compress or merge values from different columns and rows into a single row.

[ChazTime]

I have a data set that can have multiple data points for a single value, which are in different rows. I am looking to compress or merge all of these data points into a single row so I can drill down and have a clean table or line item and not multiple lines for the same data point (ID_LOC - column1). I prefer to do this creating a separate/custom table and not power query. Screenshots below.

Current View

Desired output

[ChazTime]

Sample data table below:

ID_Loc	queue_time	process_time	operation_time	category	held_time
A901_6129	0.21	1.62	1.83	processed
A901_6129				long hold	1.22
A901_6214	4.95	20.41	25.35	processed
A901_6214				hold	4.38
A901_6242	0.17	9.37	9.54	processed
A901_6242				hold	0.48
A901_6264		6.32	6.32	processed
A901_6264				long hold	0.99
A901_6264				hold	0.24

Desired outcome:

- ID_Loc matching values merged to 1 line of data

- held_time value moved up (if value exists)

- category2 split to new column if "category" is hold or long hold

- if 3 values in category (processed, hold, long hold) split to category 3 with held_time2

ID_Loc	queue_time	process_time	operation_time	category	held_time	category2	category3	held_time2
A901_6129	0.21	1.62	1.83	processed	1.22	long hold	null	null
A901_6214	4.95	20.41	25.35	processed	4.38	hold	null	null
A901_6242	0.17	9.37	9.54	processed	0.48	hold	null	null
A901_6264		6.32	6.32	processed	0.99	long hold	hold	0.24

[lbendlin]

That's a rather weird data format.  I would simplify it like this:

This will make your data digestible for Power BI.

As I understand it your operation_time value  is redundant and can be omitted.

[ChazTime]

Appreciate the response. Yes it is weird. However each data point is needed. The key is to have the ID_Loc column reduced/merged to 1 row. Each activity is a milestone of some sort, so showing those in one row with those different data points is easier to view than a table full of different milestones.

Operation time could be ommited yes, however the other numbers are needed.

[lbendlin]

The key is to have the ID_Loc column reduced/merged to 1 row.

That's the opposite of what Power BI is about.  Use a different tool.

[ChazTime]

Ouch. I wouldn't say it's the opposite. I am just seeing if I can create a more user friendly ouput for my customers. Guess I'll go back to the stone and chisel.

[lbendlin]

There's a difference between making data user friendly and making it engine friendly. But that difference melts away when you make the data engine friendly.  The structure that I proposed can then be fed into a matrix visual with one row per ID_loc.  like so:

[ChazTime]

Completely understand. Still working the problem and I appreciate your guidance. My issue is very complex as I have even more data points I did not include in my original post due to confidentiality. But again, I understand where you are going with this... I will use this as guidance. Thank you. Will mark your post as an accepted solution.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523