Add index per proudct and year using power query

[OscarSuarez10]

Hello How can I add an Index column using power query that starts in 0 like in the following table?

Product	Year	Index
A	2019	0
A	2020	1
A	2021	2
A	2022	3
B	2019	0
B	2020	1
B	2021	2
B	2022	3
C	2019	0
C	2020	1
C	2021	2
C	2022	3

[v-piga-msft · Accepted solution]

Hi @OscarSuarez10 ,

You could try the query below.  You need to group rows by Product and then create the custom column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcuxCQAwCADBXawt1C5ldAxx/zUCgYQvr7hu2aIS5ktGH8IIJ+IieZIneZKneIqneOqfOQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Year", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"all", each _, type table [Product=text, Year=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "SubCount",0,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"all"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Year", "SubCount"}, {"Custom.Year", "Custom.SubCount"})
in
    #"Expanded Custom"

Here is the output.

Best Regards,

Cherry

[HotChilli]

Add an Index (from 0) call it IndexFromZero

Add a custom column with:

Number.Mod([IndexFromZero],4))

[OscarSuarez10]

Sorry but when is 2019 I need that it appears Inex = 0 because is the first year and the same for 2020 = 1, 2021 = 2 and so on,  and I got this:

[v-piga-msft · Accepted solution]

Hi @OscarSuarez10 ,

You could try the query below.  You need to group rows by Product and then create the custom column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcuxCQAwCADBXawt1C5ldAxx/zUCgYQvr7hu2aIS5ktGH8IIJ+IieZIneZKneIqneOqfOQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Year", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"all", each _, type table [Product=text, Year=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "SubCount",0,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"all"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Year", "SubCount"}, {"Custom.Year", "Custom.SubCount"})
in
    #"Expanded Custom"

Here is the output.

Best Regards,

Cherry

[ImkeF · Accepted solution]

Hi @OscarSuarez10 ,

some time ago I recorded a screencast that shows how to perform the steps via the UI: https://www.youtube.com/watch?v=-3KFZaYImEY 

[HotChilli]

The code I posted works for the data you provided.

It depends on the data being in a similar order to your sample.

Do you want to post the real data OR if you want, just write a simple IF statement

if Year = 2019 then 0 else if Year = 2018 then...........

make sure you change the data type of Year to number before adding the column