PowerQuery Calculation based on Min value

[blytonpereira]

Hello,

I have the following column in my data - Date

I need help to add a second column named "Lag Check" that is based on the Min month number from the date column.

Eg if the earliest month number is 09 from the list of Dates, then month number 10 will be "Lag 1 and so on.

The formulas in the Lag Check column should also work when years are changing as show in the example below.

Date (Format MM/DD/YYYY)       Lag Check       

09/01/2018                                    

09/01/2018

10/01/2018                                    Lag 1          

11/01/2018                                    Lag 2              

11/01/2018                                    Lag 2               

12/01/2018                                    Lag 3               

01/01/2019                                    Lag 4               

01/01/2019                                    Lag 4            

I have attached my sample data here. The name of my query is "MI_APR".

https://drive.google.com/open?id=19dqg77i_PItkQG287ePDLE9GxPrwH_eN

Thank you.   

[Ashish_Mathur]

Hi,

Download my PBI file from here.

Hope this helps.

[Zubair_Muhammad · Accepted solution]

Hi @blytonpereira

Please see the attached file

Basically the steps are

1) Sort the Query by Date (Asc Order)
2) Group the Query by Date

3) Add an Index Column

4) Add "lag' as prefix to the Index Column

[blytonpereira]

Hi @Zubair_Muhammad thank you, your file works perfectly.

I have another problem though. My actual dataset is much larger and is mainly the refresh is extremely slow and actually doesnt even finish, it is  the Grouping part in PowerQuery that is problematic and slow.

Can you pls assist with writing a small R script for this part ? or an alternative method that does not crash.

I attached the file here that just hangs due to data volume. Basically the query is refreshing, but stops at some point and doesnt move forward.

https://drive.google.com/open?id=1gyPL3-BALBqMWX1Mu4KasypS46nk7zUV

The query is named "STAT_APR"

Thank you

[Zubair_Muhammad]

@blytonpereira

I believe the sorting in Power Query is slowing down the Query

If you can do with a calculated column it would be faster

I removed the sort by date Step and used this simple calculated column and it works fast on my PC

Please see attached file

LAG =
"LAG "
    & RANKX ( STAT_APR, [Date],, ASC, DENSE ) - 1