Forum Discussion
Index
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
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.
5 Replies
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
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.
