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

Helper III

7 years ago

Solved

Add index per proudct and year using power query

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

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
7 years ago
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