Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table of data with numbers in one column and I'd like to add a column with an index number that resets to 1 when the original column number changes. The original column is PO # below, and I'd like to add the Index column.
PO # | Index | |
104001 | 1 | |
104054 | 1 | |
104055 | 1 | |
104055 | 2 | |
104056 | 1 | |
104056 | 2 |
Solved! Go to Solution.
Hi @MardiLinke ,
Before:
After:
You can achieve this with these two alternatives.Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
a)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMTAwVIrVgTBNTRBMU6xMMxRmLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO #" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO #", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"PO #"}, {{"Grouping", each _, type table [#"PO #"=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ( [Grouping], "Index", 1 )), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"PO #", "Index"}, {"PO #", "Index"}) in #"Expanded Custom"
b)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMTAwVIrVgTBNTRBMU6xMMxRmLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO #" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO #", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"PO #"}, {{"AllRows", each SubGroupAddIndex(_), type table [#"PO #"=nullable number]}}), SubGroupAddIndex = (Table as table) as table => let #"Added Index" = Table.AddIndexColumn(Table, "Index", 1, 1, Int64.Type) in #"Added Index", #"Expanded Groups" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Index"}, {"Index"}) in #"Expanded Groups"
I got the two ways from the following blog article:
https://www.tackytech.blog/how-to-swiftly-take-over-power-query/#3_Create_ranks_and_indexes
Let me know if this helps 🙂
/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 |
Hi @MardiLinke ,
Before:
After:
You can achieve this with these two alternatives.Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
a)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMTAwVIrVgTBNTRBMU6xMMxRmLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO #" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO #", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"PO #"}, {{"Grouping", each _, type table [#"PO #"=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ( [Grouping], "Index", 1 )), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"PO #", "Index"}, {"PO #", "Index"}) in #"Expanded Custom"
b)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMTAwVIrVgTBNTRBMU6xMMxRmLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO #" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO #", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"PO #"}, {{"AllRows", each SubGroupAddIndex(_), type table [#"PO #"=nullable number]}}), SubGroupAddIndex = (Table as table) as table => let #"Added Index" = Table.AddIndexColumn(Table, "Index", 1, 1, Int64.Type) in #"Added Index", #"Expanded Groups" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Index"}, {"Index"}) in #"Expanded Groups"
I got the two ways from the following blog article:
https://www.tackytech.blog/how-to-swiftly-take-over-power-query/#3_Create_ranks_and_indexes
Let me know if this helps 🙂
/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 |
That is brilliant, thanks for the help - particularly how quickly you responded. It worked perfectly.
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |