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 

prateekraina's avatar
prateekraina
Memorable Member
8 years ago

Hi bullius,

 

You are absolutely right. You can rank and then pivot the column to achieve what you need.
Here is a sample pbix which showcases this.

 

Prateek Raina

  • bullius's avatar
    bullius
    Helper V
    8 years ago

    Thanks prateekraina. My actual dataset is much larger than the example and does not include a Rank field. My question is how do I create one? I would use the EARLIER function in DAX to make sure the ranking restarts with each new ID, but can't find an alternative in M.

    • Interkoubess's avatar
      Interkoubess
      Solution Sage
      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 

      • prateekraina's avatar
        prateekraina
        Memorable Member
        8 years ago

        Hi bullius,

         

        Interkoubess is absolutely right. I haved added his step in the same pbix.

         

        Prateek Raina

  • 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.