Power Query - Change Monthly figures to monthly rolling 12

[101Mathew]

Inside of Power Query...

Is there a method to change my current monthly figures, to a monthly rolling 12

So instead of: -

Date    Office Code    GWP

Jan18   AA                   £100

Feb18  AA                   £100

etc...

too...

Date    Office Code    GWP

Jan18   AA                   £1200

Feb18  AA                   £1200

etc...

Per office code?

Rolling 12 months is the sum of an offices GWP from the latest month back an additional 11 months

I'm guess, this would need to be some type of function but as I'm rather new to Power Query I'm unsure how to begin.

[Zubair_Muhammad · Accepted solution]

@101Mathew

You can use a Custom Column like this one
Please see the attached Excel file's Query Editor

let 
   myOffice=[Office Code],
   myDate=[Date]
       in

List.Sum(
Table.MaxN(
Table.SelectRows(SortedRows,each [Office Code]=myOffice and [Date]<= myDate),
          "Date",
          12)[GWP]
         )

[Zubair_Muhammad · Accepted solution]

@101Mathew

I think I am getting correct and fast results using the same formula with your data

I am attaching the Excel file. Please see the Query Editor

[parry2k]

@101Mathew any reason why you wnt to use 12 months rolling using Power Query, this can be easily achieve using DAX and there are many posts on rolling 12 months sum

[101Mathew]

@parry2kfirstly thanks you for your intest and reponse.

There are a number of old Excel reports that need to be maintained, and we are not permited to use Power Pivot (so no DAX) solution.

If I can get a Power Query solution, this can then be applied into these old excel reports, as power query works on both Power BI and Excel.

Thank you again for your interest.

Regards,

Mathew

[Zubair_Muhammad · Accepted solution]

@101Mathew

You can use a Custom Column like this one
Please see the attached Excel file's Query Editor

let 
   myOffice=[Office Code],
   myDate=[Date]
       in

List.Sum(
Table.MaxN(
Table.SelectRows(SortedRows,each [Office Code]=myOffice and [Date]<= myDate),
          "Date",
          12)[GWP]
         )

[101Mathew]

Hi @Zubair_Muhammad,

This is exactly what I outlined as the issue, thank you so much for your time.

When trying this on my live dataset in Excel's Power Query..., I have monthly dates from 1-1-2017 to 01/12/2018 with 95 offices, the custom column works through ~2GB, from a file that is only 17mb.

Is Power Query calling the data multipule times, would table.buffer help?

It also return some errors, not all offices codes, have data for every month I presume this is the issue, any suggestions?

Regards,

Mathew

[Zubair_Muhammad]

@101Mathew

Could you check if Query Folding is happening or not by right clicking the applied step?

If its happening, may be Table.Buffer could help

http://blog.pragmaticworks.com/power-bi-checking-query-folding-with-view-native-query

Do you have

1) Multiple Dates in a month?

2) Multiple rows for one same date?

Actually I assumed from your sample dataset that you have one single row for each month.

[101Mathew]

The dataset is not via a database, but rather via an excel file.

1) The only date per month is the 1st of each month.

2) Yes - as the date is repeated for each office code that has data (if an office code has no data, it won't have an entry)

[Zubair_Muhammad]

@101Mathew

Could you share your excel file with me?

[101Mathew]

How do you share a file on this site?

[Zubair_Muhammad]

@101Mathew

You can upload to onedrive or dropbox and share a link here

[101Mathew]

Hi @Zubair_Muhammad,

Thanks, I've not done this before, by here is a link that will last for 10 days

Link to Data Example

Thank you for your time,

Mathew

[Zubair_Muhammad · Accepted solution]

@101Mathew

I think I am getting correct and fast results using the same formula with your data

I am attaching the Excel file. Please see the Query Editor

[101Mathew]

Hi @Zubair_Muhammad,

The Clean Data (for example) worked very quickly.

My dataset however did not, I need to do a grouping first to put the data into one date per office code. 

I then do the sort step, by office then date (changing the step name in line with the 'custom column' code).

After creation of the custom column, Power query keeps running for 40mins+, a preview of 100 rows (outputted to table) shown with 100 errors. but due to length of time I needed to kill the process before it had fully completed.

I will try again with a version of my raw data after it has been grouped and outputted to a excel table.  if this works in-line with the clean data file, this would seem to suggest that the 'custom column' is not seeing the data as it appear to us users in the preview pane after the grouping process, but seeing it as it was before the grouping step.

I understand that Power Query does have some oddities in this area, and so I wonder if using Table.Buffer might resolve the issue.

Regards,

Mathew

[Zubair_Muhammad]

HI @101MathewCould you share full excel file?

I will tag @ImkeF here for help.

[ImkeF]

Please check out this function: https://gist.githubusercontent.com/ImkeF/821b98966cb543df1bd0ceab0b5f802c/raw/967bcab5b68524bd51ab00...

It is a variation of this method: https://www.thebiccountant.com/2018/09/30/memory-efficient-clustered-running-total-in-power-bi/

So make sure to group your table  on "Office Code" before applying. (You can take the default GroupKind.Global if your data isn't sorted appropriatetly by default).

[101Mathew · Accepted solution]

Hi @ImkeF, @Zubair_Muhammad, @parry2k,

Solution found, 

it was as simple as adding =Table.Buffer() in the SortRows step

= Table.Buffer(Table.Sort(#"Renamed Columns",{{"Office Code", Order.Ascending}, {"DateME", Order.Ascending}}))

This was super-fast, thanks and credit goes Ken Puls via Power Query Academy for enlightening me on this section.  I had a feeling that it had to do with power query not 'seeing' the data per the actions of the previous steps.

I would love to understand why this would be the case, if anyone has any comments.

Also big credit to @Zubair_Muhammad for the elegant code for the custome column

Finally thank you @ImkeF for putting some very interesting link up.

Thank you all

Mathew 

[101Mathew]

Hi @ImkeF,

Thank you for taking the time to contribut. I noted that your first link gist.github did gave a 404 error

I'm looking into the others

Again thank

Mathew

[101Mathew]

I will need to change all the codes, and values as the data holds sensitive data.

I'll look into doing this soon