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/2/18 | 2 |
and i want an index with following conditions:
1) only item no. 30
2) the index needs to be in the same order than date
| ID | item | index |
| aaa | 30 | 2 (ordered second time) |
| bbb | 30 | 1 (ordered first time) |
Please advise, thank you
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.
6 Replies
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 🙂
For ID aaa:
1 (first time) 2020/1/1 but no item no. 30
2 (second time) 2020/2/10 and bought item no. 30
So the index should show, ID (customer) aaa bought item no. 30 on the 2 (second time)
aaa 30 2 ID bbb, bought item no.30 on 2020/2/17, this was the first time (1) item no. 30 was ordered from ID bbb.
The second time ID bbb bought something, item no. 30 was not included, so we can ignore this.
bbb 30 1 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.
