Index

[Anonymous]

Hello everybody. Could someone help me? I have a Customers table where I have concatenated the Month and Year and I would like to create an index for each Month/Year without considering the duplicates. How could I do this?

 Thanks guys

[v-yalanwu-msft · Accepted solution]

Hi， @Anonymous ；

You could create index column then sort by group , as follows:

1.sort by date column.

2.add index column.

3.group by the date colum("all rows")

4.removing other columns.

5.Expand Column.

The final output is shown below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldFNCoAgEAXgu7gWGi0rzyIu+vEAURTdviIcgydC2483w2PGOUGqUlRp0iSkGISXL6lII5OOtEei+iF104QUkGYmU6CDqcP1Lab6SCuTxUGLvRoswbQgZQYztCGdSOZfKr2DU+lpoXRC8zmOvwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Date"}, {{"Count", each _, type table [Date=nullable date, name=nullable text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index2",1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "name", "Index2"}, {"Date", "name", "Index"})
in
    #"Expanded Custom"

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TheoC]

Hi @Anonymous 

In Power Query you can use Group By and then use Index Column (under Add Column) to add the index accordingly 🙂

Below is where you can find "Group By" in Power Query.

And here is where you can find Index:

Hope this helps.

Theo

[Anonymous]

Hi @TheoC 

Thanks for the reply.

When I use group by my other columns are affected, I end up losing my other columns or maybe I don't know how to group

[TheoC]

Hi @Anonymous 

You can duplicate the table if you want both views 🙂

[Anonymous]

 I will try. Thanks so much @TheoC