Add index only to specific rows based on condition(s)

[olimilo]

Is it possible to have an index (version number) for a specific row based on a condition? I am able to do the indexing based on the TaskID and the the TaskName (only the Upload to Server tasks) in Power Query, but only if I filter the Upload to Server task in that query (and maybe append the rest). My question is, is this possible to do in a single query (without needing to isolate the Upload to Server task) or as a calculated column? It cannot be a measure as the DocVersion is a field that could be filtered.

TaskID		TaskTimestamp		TaskName		DocVersion
10_001179		7/29/2022 19:46		Upload to Server		1
10_001179		7/29/2022 19:46		Update Request
10_001179		7/29/2022 19:47		Upload to Server		2
10_001179		7/29/2022 19:47		Update Request
10_001179		8/29/2022 16:29		Task Authorized
10_001179		3/21/2023 15:23		Update Request
10_001179		3/21/2023 15:23		Upload to Server		3
10_002734		6/27/2022 19:59		Upload to Server		1
10_002734		6/27/2022 19:59		Task Authorized
10_002734		3/31/2023 12:06		Upload to Server		2
10_002734		3/31/2023 12:06		Update Request
10_002735		6/28/2022 8:02		Update Request
10_002735		6/28/2022 13:06		Upload to Server		1
10_002735		7/30/2022 18:54		Task Authorized
10_002735		7/30/2022 18:54		Upload to Server		2
10_002735		3/31/2023 12:40		Upload to Server		3
10_002735		3/31/2023 12:41		Upload to Server		4
10_002735		3/31/2023 12:42		Upload to Server		5
10_002735		3/31/2023 12:42		Update Request

[Ahmedx]

[Brett007]

This is an excellent formula. Thank you. 

[tackytechtom · Accepted solution]

Hi @olimilo ,

I would have gone for a rather similar approach to what you have suggested. But instead of doing an append, I have chosen a merge statement on the unchanged root table.

Here the result:

Here the code in M that you can paste into the advanced editor. Just follow up steps on the right afterwards.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndHLDoIwEAXQX2lYm7SdtvSx8xd8rAgxJDTRaIJCceHXC4IaIlDLtpmTubeTJBElB0IolTpaRRKDxkAAENWGx83L/nopshy5Am1tebdllK78JM+cRRt7q23lPECG75D+HeoLYgPtyy6rzmhdu2NRnh42/xEMA20FQ1QYYP4VY2CqBkjGm4EYg/zUEHoZmSzSC4bZOxcYMn/CGTLevQGiS6W6VMoQCJmn7I9M4nVyRnqijOCe4uPCu2RQnJNwQsMJLCXDH06f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TaskID = _t, TaskTimestamp = _t, TaskName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TaskID", type text}, {"TaskTimestamp", type text}, {"TaskName", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([TaskName] = "Upload to Server")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"TaskID"}, {{"Grouping", each _, type table [TaskID=nullable text, TaskTimestamp=nullable text, TaskName=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ( [Grouping], "Index", 1 )),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"TaskTimestamp", "TaskName", "Index"}, {"Custom.TaskTimestamp", "Custom.TaskName", "Custom.Index"}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"TaskID", "TaskTimestamp", "TaskName"}, #"Expanded Custom", {"TaskID", "Custom.TaskTimestamp", "Custom.TaskName"}, "Expanded Custom", JoinKind.LeftOuter),
    #"Expanded Expanded Custom" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Custom", {"Custom.Index"}, {"Expanded Custom.Custom.Index"})
in
    #"Expanded Expanded Custom"

Let me know if this solves your issue 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[olimilo]

You know what, I completely forgot about merging tables from a previous step. This works for me just as well. Thanks Tom!