Keep the rows for the last day of each month in Query Editor

[Giorgi1989]

Hi,

I am struggling with an M-code in Query Editor. 

I have a table similar to the one below (simplified): 

Value	Volume	Bonus	Calendar Day	Month
100	200	100	01.01.2022	01
200	200	200	08.01.2022	01
200	200	200	22.01.2022	01
200	300	300	07.09.2022	09
200	500	500	11.09.2022	09
200	400	400	21.09.2022	09
200	400	400	28.09.2022	09
200	4200	4200	21.11.2022	11
200	200	100	31.11.2022	11

I want to filter the rows so that only the max day in the date column ('Calendar Day') for each month is returned. 

So, in a nutshell, the desired output would look something like this:

Value	Volume	Bonus	Calendar Day	Month
200	200	200	22.01.2022	01
200	400	400	28.09.2022	09
200	200	100	31.11.2022	11

Any help would be appreciated.

P.S. I have tried List.Max, 'Group by Month' and other solutions to no avail.

[Giorgi1989]

Thank you for the comment. The (memory) problem in our case may lie more with the fact that we use SAP Business Warehouse as our source, which has placed a great deal of limitations on us in the past. There is an organizational decision to move away from SAP BW in the future, but that won't happen anytime soon. 

[BA_Pete · Accepted solution]

Hi @Giorgi1989 ,

Group your table on [Month] and set the aggregation column to 'All Rows' and call it 'data'.

Create a new custom column with the following code:

Table.Max([data], "Calendar Day")

Expand the resulting record column to reinstate the grouped columns for only rows where [Calendar Day] = max date in month.

Power Query example code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY/RCcAgDER3ybeUu2tLdRZx/zUqViRtEYTwkpBHSHI2AhZMjU8NbjUEqTVWQu5zT8QVS5pZuyOuDWlYyVmnIzmzDketWXFqvVLdxnE//1+yf/Gxyg0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, Volume = _t, Bonus = _t, #"Calendar Day" = _t, Month = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}, {"Volume", Int64.Type}, {"Bonus", Int64.Type}, {"Calendar Day", type date}, {"Month", Int64.Type}}),
    groupMonthAllRows = Table.Group(chgTypes, {"Month"}, {{"data", each _, type table [Value=nullable number, Volume=nullable number, Bonus=nullable number, Calendar Day=nullable date, Month=nullable number]}}),
    addMaxDateRecord = Table.AddColumn(groupMonthAllRows, "maxDateRecord", each Table.Max([data], "Calendar Day")),
    expandMaxDateRecord = Table.ExpandRecordColumn(addMaxDateRecord, "maxDateRecord", {"Value", "Volume", "Bonus", "Calendar Day"}, {"Value", "Volume", "Bonus", "Calendar Day"}),
    remOthCols = Table.SelectColumns(expandMaxDateRecord,{"Value", "Volume", "Bonus", "Calendar Day", "Month"})
in
    remOthCols

Example output:

Pete

[Giorgi1989]

Thank you immensely for your kind reply, and my sincere apologies for returning so late to this. I have had personal reasons for this, which I won't tire you here with unnecessarily. Generally speaking, your proposed method indeed works fine. Unfortunately, however, in my specific application it did not help me overcome the problem, which I had. Let me clarify this a bit further:

In my real-life scenario I had about 20 or so columns to work with. When I applied all the suggested steps to them, I ran into some memory issues, whereby it was stated that not enough memory was available to handle the amount of data. One of the reasons I wanted to only have the latest days filtered by month, was exactly to bypass this memory problem. Indeed, if I were to leave all days of month in, then for 12-month period I would be winding up with 50+ million rows, which seems to be too much to be handled by PBI (at least with our current subscription of PBI Pro). What I had hoped for was to be able to first filter the dates to the latest calendar day, and then, as next steps, add all those columns in, which greatly multiply the number of rows. But, I seem to be unable to do that. On the other hand, if I include all the columns that I later need for my reporting, and only then apply those 'last calendar day' filtering steps (suggested by you), I run into the memory issues. I feel like I am in a Catch-22 situation 😄 

[BA_Pete]

Hi @Giorgi1989 ,

50 million rows is nothing for Power BI, it can handle literally billions (unless you're reaching PBIX file size limits, but this wouldn't throw an error until publishing). The issue is most likely with your hardware, more specifically, the amount of RAM you have on the machine where you're doing your processing. This can be cheaply and easily upgraded for instant results. For context, I have 48GB RAM on my dev machine.

If you're not able to upgrade your hardware, there's a couple of other options available. In order of preference:

1) Limit the data at source by using views or similar. If your source is an SQL Server, then you should be able to get the outcome you need using max('Calendar Day') over (partition by Month) and Group By.

2) Change this table, or the whole model, to Direct Query. Using DQ will avoid any data being loaded into your report, so avoids processing limitations like this. However, your PQ transformations will be limited to only those that fold back to your source, and you will need to work a bit harder at the visualisation stage to maintain good, responsive, report performance (as every visual will send its own query direct to your source to display).

Pete