Increase Index Column by 1 when Text is Present Power Query

July 15, 2022

Attempting to add a conditional sort of index column using PQ that increases only by one when "X" is present starting from 0.

e.g:

>Solution :

Filter column for X, add index, recombine with original data filtered for non-X.

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Column1] = "X")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
combined = #"Added Index" & Table.SelectRows(Source, each [Column1] <> "X")
in  combined

If combine order matters, then add initial index and resort later:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index1" = Table.AddIndexColumn(Source, "temp", 0, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index1", each ([Column1] = "X")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
combined = #"Added Index" & Table.SelectRows(#"Added Index1", each [Column1] <> "X"),
#"Sorted Rows" = Table.Sort(combined,{{"temp", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"temp"})
in  #"Removed Columns"