EARLIER function in M/Query Editor

[bullius]

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:

ID	Name1	Name2	Name3
1	Apple	Banana
2	Orange
3	Pear
4	Pineapple	Grape	Mango
5	Peach

I plan to do this by ranking the names per ID, e.g.

ID	Name	Rank
1	Apple	1
1	Banana	2
2	Orange	1
3	Pear	1
4	Pineapple	1
4	Grape	2
4	Mango	3
5	Peach	1

...then pivoting the table using the rank field. I don't know if this will work, but to do it I need to use something like the EARLIER function in DAX. Anyone know an alternatinve in M? (Or a better way to achieve my end goal).

Thanks!

[prateekraina]

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

[alfranco17]

Hi Patreek.

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

Thank you.

Armando.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[alfranco17]

Thanks for your reply, Ashish.

This is what I have:

Name	Date
Alice	2019-01-01
Alice	2019-01-03
Alice	2019-01-14
John	2019-01-03
John	2019-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.

Name	Date	Visit
Alice	2019-01-01	1
Alice	2019-01-03	2
Alice	2019-01-14	3
John	2019-01-03	1
John	2019-01-12	2

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]

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

[bullius]

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 · Accepted solution]

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 

[bullius]

Thanks! That works.

[prateekraina]

Hi @bullius,

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

Prateek Raina