0
288
ViewsHow do one group index number by a custom category in powerquery
How can one group index number by a custom category in this image show below. The challenge is on the left section, while the expected output is shown by an arrow at the right section. I want for for every ID that has row containing G-TAN, should have a seperate index number, so also K-TAN, M-TAN according to this format as shown in the image below:
Thank you.
·
Santiago Trujillo
1 answers
Answer question0
try
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"FIKA"}, {{"Count", each _, type table}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1001, 1000, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Table.AddIndexColumn([Count],"ID2", [Index], 1, Int64.Type)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count", "Index"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"DOSE", "UNIT", "QTY", "ID2"}, {"DOSE", "UNIT", "QTY", "ID2"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"ID2", "ID"}})
in #"Renamed Columns"
| ID | FIKA | DOSE | UNIT | QTY |
|---|---|---|---|---|
| 1 | G | 3 | 1 | 2 |
| 2 | G | 2 | 1 | 5 |
| 3 | G | 1 | 1 | 2 |
| 4 | G | 2 | 1 | 3 |
| 5 | K | 1 | 1 | 5 |
| 6 | K | 2 | 1 | 2 |
| 7 | K | 1 | 1 | 5 |
| 8 | M | 3 | 1 | 2 |
| 9 | M | 1 | 1 | 2 |
·
Santiago Trujillo
Report
Answer question