cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bullius Member
Member

EARLIER function in M/Query Editor

Hi

 

I have data that looks like this:

 

IDName
1Apple
1Banana
2Orange
3Pear
4Pineapple
4Grape
4Mango
5Peach

 

I want it to look like this:

 

IDName1Name2Name3
1AppleBanana 
2Orange  
3Pear  
4PineappleGrapeMango
5Peach  

 

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

 

IDNameRank
1Apple1
1Banana2
2Orange1
3Pear1
4Pineapple1
4Grape2
4Mango3
5Peach1

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Interkoubess Established Member
Established Member

Re: EARLIER function in M/Query Editor

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 

View solution in original post

9 REPLIES 9
prateekraina New Contributor
New Contributor

Re: EARLIER function in M/Query Editor

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 Member
Member

Re: EARLIER function in M/Query Editor

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 Established Member
Established Member

Re: EARLIER function in M/Query Editor

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 

View solution in original post

prateekraina New Contributor
New Contributor

Re: EARLIER function in M/Query Editor

Hi @bullius,

 

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

 

Prateek Raina

bullius Member
Member

Re: EARLIER function in M/Query Editor

Thanks! That works.

alfranco17 Frequent Visitor
Frequent Visitor

Re: EARLIER function in M/Query Editor

Hi Patreek.

 

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

 

Thank you.

Armando.

Super User
Super User

Re: EARLIER function in M/Query Editor

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
alfranco17 Frequent Visitor
Frequent Visitor

Re: EARLIER function in M/Query Editor

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.

Highlighted
alfranco17 Frequent Visitor
Frequent Visitor

Re: EARLIER function in M/Query Editor

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 134 members 1,643 guests
Please welcome our newest community members: