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

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/