Hi, I have two columns of data the first containing a number the second text. The serial number starts at one and can go up to 40 restarting at random points. I need to automatically create a column that groups the serial number and increments each time the serial number goes back to 1. Can't seem to find a solution, any help would be greatly appreciated. JPG of the required result is provided in the screenshot with the new column labelled as Group.
Many thanks
Simeon
Hi Simeon,
Please provide a sample file when you post a question so we don't have to recreate your data.
You can do this with a combination of Index Columns and Merging the query with itself:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Data Number] = 1 then
Text.From([Data Number]) & "-" & Text.From([Index]) else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Custom] <> null)),
#"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Custom"}, #"Added Index1", {"Custom"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Index"}, {"Index"}),
#"Filled Down" = Table.FillDown(#"Expanded Added Index1",{"Index"}),
#"Removed Columns1" = Table.RemoveColumns(#"Filled Down",{"Custom"})
in
#"Removed Columns1"
Please see the attached file.
Regards
Phil
Excellent, Many thanks Phil. Also provided a very good learning opportunity.
Cheers
Simeon
No worries
