Forum Discussion

bullius's avatar
bullius
Helper V
8 years ago
Solved

EARLIER function in M/Query Editor

Hi   I have data that looks like this:   ID Name 1 Apple 1 Banana 2 Orange 3 Pear 4 Pineapple 4 Grape 4 Mango 5 Peach   I want it to look like this:   ...
Need Help
  • Interkoubess's avatar
    Interkoubess
    8 years ago

    HI bullius,

     

    Yes it is possible to do that in M.

    To sum up group by your id and add an index.

    You can add this code into your steps: 

    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"ID"}, {{"Count", each  Table.AddIndexColumn(_, "Index", 1, 1), type table}})

     

    Hope it helps...

     

    Ninter 

alfranco17's avatar
alfranco17
Advocate I
7 years ago

Hi Patreek.

 

I have the same question. If you have it at hand, would you please repost your pbix?

 

Thank you.

Armando.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

Share some data and show the expected result.

  • alfranco17's avatar
    alfranco17
    Advocate I
    7 years ago

    Thanks for your reply, Ashish.

     

    This is what I have:

    NameDate
    Alice2019-01-01
    Alice2019-01-03
    Alice2019-01-14
    John2019-01-03
    John2019-01-12

     

    let
    Source = #table( type table[Name=text, #"Date"=date], { {"Alice",#date(2019,1,1)},{"Alice",#date(2019,1,3)}, {"Alice",#date(2019,1,14)}, {"John",#date(2019,1,12)}, {"John",#date(2019,1,3)} } )
in
    Source

    And here is what I need. I want to know the number of the visit by date.

    NameDateVisit
    Alice2019-01-011
    Alice2019-01-032
    Alice2019-01-143
    John2019-01-031
    John2019-01-122

     

    I was able to get this using DAX, but I would like to have it there in the query.

    Visit =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Name] = EARLIER ( 'Table'[Name] )
            && 'Table'[Date] <= EARLIER ( 'Table'[Date] )
    )
)

    Thanks.

    Armando.

    • alfranco17's avatar
      alfranco17
      Advocate I
      7 years ago

      Found a solution. I could not create it from scratch, but here it is. Is there an easier way?

      let
    Source = #table( type table[Name=text, #"Date"=date], { {"Alice",#date(2019,1,1)},{"Alice",#date(2019,1,3)}, {"Alice",#date(2019,1,14)}, {"John",#date(2019,1,12)}, {"John",#date(2019,1,3)} } ),
    Table1 = Table.TransformColumnTypes(Source,{{"Date", type text}}),
    AddCount = Table.AddColumn(
              Table1, 
              "Visit", //(a) is a parameter for function, which equals current record, and function should return value for new cell of "SubcategoryRanking"
              (a)=> Table.RowCount( 
                        Table.SelectRows( 
                        Table1, //(b) equals whole table1. This function returns table filtered by given criteria
                        (b) =>  b[Name] = a[Name] and b[Date] < a[Date]) 
                    ) + 1,
              Int64.Type),
    #"Sorted Rows" = Table.Sort(AddCount,{{"Name", Order.Ascending}, {"Visit", Order.Ascending}})
in
    #"Sorted Rows"

      Thanks!

      Armando