Forum Discussion
index per group
Hi, I have following table ID date item no. aaa 2020/1/1 1 aaa 2020/1/1 2 aaa 2020/2/10 30 aaa 2020/2/10 6 bbb 2020/2/17 1 bbb 2020/2/17 30 bbb 2020/...
Hi Anonymous ,
Please check:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxMVNJRMjIwMtA31DcEMhUMlWJ1sIkboYsb6RsagCSMDXDJmIElkpKSkCTMEXZgkYCahSpjAZIB2R8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, date = _t, #"item no." = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"date", type date}, {"item no.", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "date"}, {{"Count", each _, type table [ID=nullable text, date=nullable date, #"item no."=nullable number]}}), #"Grouped Rows1" = Table.Group(#"Removed Columns", {"ID"}, {{"Count", each _, type table [ID=nullable text, date=nullable date, Count=table]}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([Count],"Index", 1, 1)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"date", "Count", "Index"}, {"date", "Count.1", "Index"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Count"}), #"Expanded Count.1" = Table.ExpandTableColumn(#"Removed Columns1", "Count.1", {"item no."}, {"item no."}), #"Filtered Rows" = Table.SelectRows(#"Expanded Count.1", each ([#"item no."] = 30)) in #"Filtered Rows"BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Add this as a new calculated column in your model:
Index =
VAR _ID = [ID]
VAR _RANK =
RANKX(
FILTER(IndexTable,IndexTable[ID] = _ID),
IndexTable[date],,ASC,Dense
)
RETURN
IF( IndexTable[item no.] = 30, _RANK,BLANK())
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply š