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.
Sample data table below:
- 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
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.
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.
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.
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:
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.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Please show the expected outcome based on the sample data you provided.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.