Power Query - Insert Rows based on the number of records within a category

[CliffordAP]

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!

[Vijay_A_Verma · Accepted solution]

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"

[CliffordAP]

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?