Forum Discussion

clim2f88j's avatar
clim2f88j
Frequent Visitor
2 years ago

Index Column Based on ID and Date

Hello everyone,   I need to add an index column based on consumers ID and date.   Data: ID Date AAA 1/1/23 AAA 3/7/23 AAA 4/18/23 BBB 12/19/22 BBB 4/5/23 CCC 7/9/23 ...
Need Help
Sammyben's avatar
Sammyben
New Member
2 years ago

I have same question but I need to create by id then category and then by date. Hiw can I add also the category?

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi Sammyben, something like this? (if no provide sample data and expected result please)

 

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRcjYEEob6hvpGBkbGSrE6KOLG+uYY4kY4xEHqTfQNLRASTk5OcAuM9A0tQTJG6DIm+qYYOoxQxGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Category = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Date", type date}}, "en-US"),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID", "Category"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table}}),
    #"Combined Tables" = Table.Combine(#"Grouped Rows"[Data])
in
    #"Combined Tables"