Forum Discussion
Power Query Editor: add increment step for each entry with the same ID
Anonymous
It's a minor variation on the previous version:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRCkkszlYwBDKCS1ILFPISc1MVEpOSlWJ18ChITUvHr6CisgqswNQcrsAIRUFyUiJ+BSmpaWAFlhbmMAXGKAoyMrMgbjCwhCkwQVGQk5sHscIMboUpioK8/AL8CgqLisEKzM1MYQrMUBSUlJYpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Task Name" = _t, #"Step Name" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Step", each Table.AddIndexColumn(_, "Step", 1, 1, Int64.Type)}}), #"Expanded Step" = Table.ExpandTableColumn(#"Grouped Rows", "Step", {"Task Name", "Step Name", "Step"}, {"Task Name", "Step Name", "Step"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Step",{{"Task Name", type text}, {"Step Name", type text}, {"Step", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "ID_Step", each Text.From([ID]) & "-" & Text.From([Step]), type text), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"ID", "Step", "ID_Step", "Task Name", "Step Name"}) in #"Reordered Columns"Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Anonymous
You can create two calculated columns:
Step = CALCULATE ( COUNT ( Table1[Step Name] ), Table1[Step Name] <= EARLIER ( Table1[Step Name] ), ALLEXCEPT ( Table1, Table1[ID] ) )ID_Step = Table1[ID] & "-" & Table1[Step]Do note though that you, as it is now, you do not have a column to establish order in the table in DAX. You would either have to add an index at the source (like you'd do it in PQ) or a possible alternative would be to use Step Name to establish that order (alphabetically), which is what I have done here.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Community ChampionHi Anonymous
Great that you show the expected result but please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
Place the following M code in a blank query to see the steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorVwaRNzVFpSwtziLyBJUTczBiFNjczVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Step", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)[Index]}}),
#"Expanded Step" = Table.ExpandListColumn(#"Grouped Rows", "Step"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Step",{{"Step", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.From([ID]) & "-" & Text.From([Step]), type text)
in
#"Added Custom"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thanks for the quick reply.
Is it possible to retain data from other columns that are also present?
So for example, as well as an ID number, there is also a Task Name and Step name.
I managed to create the increments against each of the IDs using your solution but I seem to have lost the Task Names and Step names and can't see how to add them back into the data.
Apologies these were not in my initial sample data as I didn't realise they would disappear.
Sample data:
ID Task Name Step Name
123 Task 1 Step name abc
123 Task 1 Step name efg
123 Task 1 Step name xyz
573 Task 2 Step name cba
573 Task 2 Step name def
987 Task 3 Step name hij
109 Task 4 Step name lmn
563 Task 5 Step name nop
563 Task 5 Step name qrs
765 Task 6 Step name tuv
Desired Result:
ID Step Custom Task Name Step Name
123 1 123-1 Task 1 Step name abc
123 2 123-2 Task 1 Step name efg
123 3 123-3 Task 1 Step name xyz
573 1 573-1 Task 2 Step name cba
573 2 573-2 Task 2 Step name def
987 1 987-1 Task 3 Step name hij
109 1 109-1 Task 4 Step name lmn
563 1 563-1 Task 5 Step name nop
563 2 563-2 Task 5 Step name qrs
765 1 765-1 Task 6 Step name tuv
Is it it possible to retain the task and step name columns?
PS thank you for the post guideance also, the sample is now in selectable format and not an image.
PPS I tried laying the data in a table but kept this error: "Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied."