Any ideas on how to "flatten" rows together in Power BI?

[ianallen13V2]

Hello, 

I'm looking to see if anyone has any insight into this issue.  

I have a table (below) and sadly the data is what we are getting from a vendor at this time, we are looking at options for clean up, but want to see if anyone knew of a way where I could 'Flatten" or Merge the rows together by position_title so that there is only one unique value, and then whatever value is in position_location, that gets filled, and same with Position_start_date, etc. 

So here is my example table: 

And what I WANT to see is (for example): 

So that it combines all values that tie into that position title.

I tried to do a Group By, but that didn't seem to work, so I'm open to any ideas?

[HotChilli · Accepted solution]

1. Group by position_title then  (using Advanced section) add aggregations for every column (MAX will probably work on each one)

[ianallen13V2]

Wow! That was easier than I thought. I just needed to reorder some columns and then I was missing the a

[bolfri]

Is there any possibility for you to share some sample data? Or just a file with dummy data to show you how to solve this problem?

[ianallen13V2]

I added the data (only one individual) to an excel file, changed some names, etc. but this is basically what I am using in a nutshell.  How can I upload a file here?

[HotChilli]

You are on the right track with the Group By.  Where did it go wrong?

[ianallen13V2]

I'm not 100% sure how to get it to work right, I try...

1. Group by position_title, call new column name "ALL" and select operation as "ALL ROWS" 

2. Next - now I have a column called "ALL" with tables - but after that - I'm only allowed to expand, which is not what I want.

 and some of the tables are really just duplicates, which I just want 'merged' into one record (see example):

So just looking for one line with one record with company name, position title, location (if available) and start / end dates all on the same line. 

[AbhinavJoshi]

Hello @ianallen13V2. You can achieve a similar result using unpivot columns. Please see the attached screenshot and let me know if it works for you. Thanks

[ianallen13V2]

Hello, 

Thank you for responding - but that is not what I need sadly.  I need one row for with all values like my example stated.  

So one row with combined job titles, job location, job dates, etc.

Like the example below: 

Any thoughts?