Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
This is an excellent formula. Thank you.
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
You know what, I completely forgot about merging tables from a previous step. This works for me just as well. Thanks Tom!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
104 | |
87 | |
73 | |
66 |
User | Count |
---|---|
122 | |
112 | |
98 | |
79 | |
72 |