Power Query - Merging rows of data based on unique ID
I have a problem similar to the one detailed here and am employing the pivot-unpivot solution, which is working well so far. My data is more complex though, and as it is drawing from multiple sources sometimes there are discrepant values.
Essentially - after applying the pivot/unpivot, the grouping works perfectly but I end up getting a lot of errors. All of them appear to be the same:
Expression.Error: There were too many elements in the enumeration to complete the operation.
Details: List
In an effort to resolve this, I added a 5th parameter to my Pivot.Column command:
each Text.Combine(_, "#(lf)")
This results in the errors showing the values being combined instead. However, sometimes the values displayed will be the exact same. How can I get these to actually merge, while only showing an error/cell values in the cells with discrepant data? I am new to power query and not sure if there is a better solution than "Text.combine"
Some examples below... Thanks for your help
Merged table looks something like:
| Unique ID | Data A | Data B | Data C |
|---|---|---|---|
| ABC | 123 | 789 | null |
| ABC | 123 | null | name2 |
| BCD | 234 | null | null |
| BCD | null | null | null |
| BCD | 1234 | null | name2 |
| EFG | 333 | 222 | name1 |
| EFG | null | 222 | null |
| ABC | null | null | null |
Following pivot/unpivot with text combine (I am not sure how to show line breaks here, so have delineated using a comma):
| Unique ID | Data A | Data B | Data C |
|---|---|---|---|
| ABC | 123, 123 | 789 | name2 |
| BCD | 234, 1234 | null | name2 |
| EGF | 333 | 222, 222 | name1 |
What I want:
| Unique ID | Data A | Data B | Data C |
|---|---|---|---|
| ABC | 123 | 789 | name2 |
| BCD | 234, 1234 | null | name2 |
| EGF | 333 | 222 | name1 |
Where the Data A point for BCD would be an error, so I can see that there's something that needs to be fixed in the source data tables.
With your data like this:
| Unique ID | Data A | Data B | Data C |
|---|---|---|---|
| ABC | 123 | 789 | null |
| ABC | 123 | null | name2 |
| BCD | 234 | null | null |
| BCD | null | null | null |
| BCD | 1234 | null | name2 |
| EFG | 333 | 222 | name1 |
| EFG | null | 222 | null |
| ABC | null | null | null |
Right click the Unique ID column, select "Unpivot Other Columns"
Change the resulting Value column type to "Text"
Select all Columns. Right click, choose "Remove Duplicates".
Select the Attribute column. Choose Pivot from the Transform Tab. Choose Values column from the drop down. Choose Don't Aggregate under Advanced options. Add your existing code as the fifth parameter each Text.Combine(_, "#(lf)")
