Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

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

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





Frequent Visitor

Sample data table below:

A901_6129   long hold1.22
A901_6214   hold4.38
A901_6242   hold0.48
A901_6264 6.326.32processed 
A901_6264   long hold0.99
A901_6264   hold0.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

A901_61290.211.621.83processed1.22long holdnullnull
A901_6264 6.326.32processed0.99long holdhold0.24

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.

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.

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.

Super User
Super User

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.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

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!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors