PQ - Grouping rows with different data type
| Source |
|
|
Result |
|
|
|
|
|
|
| ToGroup |
Result |
|
ToGroup |
Result |
| A |
20 |
|
A |
40 |
| B |
|
|
B |
|
| C |
15 |
|
C |
15 |
| A |
20 |
|
|
|
| B |
|
|
|
|
| ToGroup |
Result |
|
ToGroup |
Result |
| A |
20 |
|
A |
40 |
| B |
100 |
|
B |
100 |
| C |
15 |
|
C |
15 |
| A |
20 |
|
|
|
| B |
|
|
|
|
| ToGroup |
Result |
|
ToGroup |
Result |
| A |
20 |
|
A |
40 |
| B |
5 |
|
B |
15 |
| C |
15 |
|
C |
15 |
| A |
20 |
|
|
|
| B |
10 |
|
|
|
| ToGroup |
Result |
|
ToGroup |
Result |
| A |
20 |
|
A |
40 |
| B |
John |
|
B |
MixedDataType |
| C |
15 |
|
C |
15 |
| A |
20 |
|
|
|
| B |
10 |
|
|
|
| ToGroup |
Result |
|
ToGroup |
Result |
| A |
20 |
|
A |
40 |
| B |
John |
|
B |
John, Mark |
| C |
15 |
|
C |
15 |
| A |
20 |
|
|
|
| B |
Mark |
|
|
|
Code:
// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Group(Source, {"ToGroup"}, {{"Result", each try try List.Sum([Result]) otherwise Text.Combine(List.Distinct([Result]), ", ") otherwise "MixedDataType", type any}})
in
Group
