Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MardiLinke
New Member

Power Query - create an index that restarts when another column changes

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

 

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi  @MardiLinke , 

 

Before:

tackytechtom_7-1689131002209.png

 

 

After:

tackytechtom_6-1689130979122.png

 

 

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! linkedIn

#proudtobeasuperuser 

View solution in original post

2 REPLIES 2
tackytechtom
Super User
Super User

Hi  @MardiLinke , 

 

Before:

tackytechtom_7-1689131002209.png

 

 

After:

tackytechtom_6-1689130979122.png

 

 

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! linkedIn

#proudtobeasuperuser 

That is brilliant, thanks for the help - particularly how quickly you responded. It worked perfectly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.