I have one column of input with each set of data (two rows each) separated by an empty row. What is the best way to add a 2nd column showing the separate groups? For example:
Name
A
A
B
B
Should like:
Name Group
A 1
A 1
B 2
B 2
I have also attached an example file.
Hi,
A couple of questions if I may:
Does it actually matter what the group numbers are, as long as the grouping works?
Also, do you actually want the blank rows in the output table?
Thank you for helping.
The group number does not matter as long as each two are paired together. And, I keep the blank row to show separation from each group. This is a match schedule and the final display will be 20 individual matchups so I keep the blank row between each match for clarity.
You could do something simple like this then:
let
Source = Excel.CurrentWorkbook(){[Name="Matchups"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Grouper", each if [Team] = null then [Index] else null),
#"Filled Up" = Table.FillUp(#"Added Custom",{"Grouper"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Index"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "GroupNumber", each if [Team] = null then null else [Grouper]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Grouper"})
in
#"Removed Columns1"
Okay, I see what you did here; I can follow every step. Yeah, that works. Just "got stuck."
Thank you for the help.
My apologies, I spoke too soon. With regard to the last group, after #"Added Custom" = Table.AddColumn(#"Added Index", "Grouper", each if [Team] = null then [Index] else null), both are null. Thus, the next command, Table.FillUp, is filling up a null (no number).
