Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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/...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

Fowmy's avatar
Fowmy
Super User
6 years ago


Anonymous 

What do mean by "ordered second time" and "ordered first time"?
can you explain?

________________________

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 šŸ™‚

YouTube, LinkedIn

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Fowmy 

     

    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)

     

    aaa302

     

     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.

     

    bbb301
    • Icey's avatar
      Icey
      Community Support
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        Icey thanks, that was exactly was I was looking for.