Power Query - Merging rows of data based on unique ID

Question

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.

Answer 1

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)")

Answer 2

right click the UniqueID column and unpivot other columns

transform .. data type ... text for the 3 columns

click select all 3 columns, right click, remove duplicates

click select Unique ID and Attribute columns, right click, group by ... keep default options, click ok

in formula bar change end of grouping formula

from

each Table.RowCount(_), Int64.Type}})

to

each Text.Combine(List.Transform([Value], Text.From), ","), type text}})    

or

each Text.Combine(List.Transform([Value], Text.From), "#(lf)"), type text}})    

Click select Attribute column

Transform ... pivot column ... values column:count, advanced options: dont aggregate

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Unique ID"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Unique ID", type text}, {"Attribute", type text}, {"Value", type text}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type1"),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Unique ID", "Attribute"}, {{"Count", each Text.Combine(List.Transform([Value], Text.From), ", "), type text}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Count")
in  #"Pivoted Column"