Forum Discussion
Power Query - Insert Rows based on the number of records within a category
Hello all!
I have a small puzzle that concerns inserting rows.
1 | A |
| 2 | A |
| 3 | B |
| 4 | B |
| 5 | C |
| 6 | C |
| 7 | C |
Notice in Column2, we have categorical data that varies in the number of instances that each category appears.
I'm trying to figure out a way that we can insert rows to makes the number of instances for each category equal. Since 'C' shows up 3 times, I would like to insert rows such that 'A' and 'B' show up 3 times as well.
The result table would be:
| 1 | A |
| 2 | A |
| A | |
| 3 | B |
| 4 | B |
| B | |
| 5 | C |
| 6 | C |
| 7 | C |
Column1 would have rows inserted into it that are blank, and Column2 would have an equal set of instances for each category (in this case, 3 instances each).
I'm a little new at this, and the documentation isn't all that helpful for beginners.
Thank you!
Solution uploaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuR4l1xMoEZxIPGuu?e=KNpNhf
Use below query
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Column2"}, {{"Count", each Table.RowCount(_), Int64.Type}}), #"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Column2", "Column2 - Copy"), #"Added Custom" = Table.AddColumn(#"Duplicated Column", "Custom", each {1..List.Max(#"Duplicated Column"[Count])-[Count]}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [Custom] <> null), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column2 - Copy"}), #"Appended Query" = Table.Combine({#"Changed Type", #"Removed Other Columns"}), #"Added Index" = Table.AddIndexColumn(#"Appended Query", "Index", 0, 1, Int64.Type), #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each if [Column1]=null then [#"Column2 - Copy"] else [Column2]), #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Custom", Order.Ascending}, {"Index", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Column2", "Column2 - Copy", "Index"}) in #"Removed Columns"
2 Replies
Solution uploaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuR4l1xMoEZxIPGuu?e=KNpNhf
Use below query
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Column2"}, {{"Count", each Table.RowCount(_), Int64.Type}}), #"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Column2", "Column2 - Copy"), #"Added Custom" = Table.AddColumn(#"Duplicated Column", "Custom", each {1..List.Max(#"Duplicated Column"[Count])-[Count]}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [Custom] <> null), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column2 - Copy"}), #"Appended Query" = Table.Combine({#"Changed Type", #"Removed Other Columns"}), #"Added Index" = Table.AddIndexColumn(#"Appended Query", "Index", 0, 1, Int64.Type), #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each if [Column1]=null then [#"Column2 - Copy"] else [Column2]), #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Custom", Order.Ascending}, {"Index", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Column2", "Column2 - Copy", "Index"}) in #"Removed Columns"I learned a lot from this,
Thank you very much!I see on the third line for "#GRouped Rows", there is a section that says
Table.RowCount(_)
Do you understand why there is an underscore in this function?
