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 ChampionAnonymous
That's weird. I just typed in the below table in Excel, copied it and pasted it here. No problems:
| Col1 | Col2 |
| 1 | 3 |
| 2 | 4 |
| 3 | 5 |
Try not formatting the data as table in Excel. Although it should work as well. Otherwise share the Excel file with the data (or the pbix). You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).
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
No matter what I try it just won't accept any kind of table - be it copied in from excel or just creating a table and filling it out.
I uploaded the sample file to DropBox but it won't even let me post the link. I just get the same 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."
- AlB
Anonymous
I have no idea what is going on. Perhaps log out, restart the browser and log in again. It must have gotten stuck somewhere.
Or send me the Dropbox link by private message
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
I have signed out/back in and cleared all tempory file and the error persists. I cannot post any links or tables or any kind of HTML. Is there a way to raise a ticket as there is clearly an issue.
I will try and DM you the dropbox file.