Forum Discussion
How to merge rows base on certain value (ID)
Hello All,
Hope you are doing well.
Need help on this matter. I have this kind of table with duplication of ID but some of the rows with the same ID completes each other if we can transform them into one column.
| ID | Name | Age | Department | Jan Sales | Feb Sales |
| 1 | Jose | 25 | null | 2000 | null |
| 1 | Jose | 25 | null | null | 7000 |
| 2 | Imran | 22 | null | 5000 | null |
| 2 | Imran | 22 | null | null | 5000 |
| 3 | Sophea | 30 | null | 20000 | null |
| 3 | Sophea | 30 | null | null | 300 |
I want to transform this table in power query to become this table:
| ID | Name | Age | Department | Jan Sales | Feb Sales |
| 1 | Jose | 25 | null | 2000 | 7000 |
| 2 | Imran | 22 | null | 5000 | 5000 |
| 3 | Sophea | 30 | null | 20000 | 300 |
Please help if anyone has any idea how I could achieve this.
Thank you!
Anonymous
You need two steps to achieve it.
First, select columns from ID to Department, then Choose unpivot other column,Then, select the Attribute column, under transform Tab, choose, Pivot Column, Make your selection in the dialog box as follows
Result
6 Replies
- Fowmy
Super User
Anonymous
You need two steps to achieve it.
First, select columns from ID to Department, then Choose unpivot other column,Then, select the Attribute column, under transform Tab, choose, Pivot Column, Make your selection in the dialog box as follows
Result
Thanks Fowmy for the solution you sugested.
What if I have another column that a bit inconsistance. Observe column 'Supervisor'. Just to mention, the data type of 'Supervisor' column is 'any' or 'text'. Tried your sugesstion but not working for this type table.ID Name Age Department Supervisor Jan Sales Feb Sales 1 Jose 25 null null 2000 null 1 Jose 25 null null null 7000 2 Imran 22 null Natalie 5000 null 2 Imran 22 null null null 5000 3 Sophea 30 null Alex 20000 null 3 Sophea 30 null null null 300 - Fowmy
Anonymous
Based on your updated data, can you share the expected result ?
It's not clear how the supervisor field is related to each ID here.
MBredenHelper I
add a step between unpivot and pivot and filter without null:
let Quelle = Excel.CurrentWorkbook(){[Name="nasriq_3"]}[Content], #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(Quelle, {"ID", "Name", "Age", "Department"}, "Attribute", "Value"), #"Gefilterte Zeilen" = Table.SelectRows(#"Entpivotierte andere Spalten", each ([Value] <> "null")), #"Pivotierte Spalte" = Table.Pivot(#"Gefilterte Zeilen", List.Distinct(#"Gefilterte Zeilen"[Attribute]), "Attribute", "Value") in #"Pivotierte Spalte"