Log In
Register
Home
New Member
January 15, 2022
Offline
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
October 5, 2010
Offline
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
1 Guest(s)
