Get last Month End Data using Power Query

[Marcel_]

Hi there, 

I hope you are all doing well!

I have a table with a lot of columns, coming from our ERP. I want to use Power Query so that everytime I pull the data from it and insert it in a specific folder, it'll instantly format it and be ready to use.

I am saying that as an introduction because I am able to get the result I want by using helper columns in Excel, but can't exactly replicate one helper column in Power Query.

Well, this is my sample data I get from ERP: 

Product ID	Date	Pallets
2505	18/03/21	5
2505	18/03/21	6
2505	20/03/21	4
33650	01/10/21	2
33650	21/10/21	33
20000	12/12/21	10

For some reasons, I only want to get the last row of each month as in - using helper columns in Excel (in italic below) :

Product ID	Date	Pallets	Index	Product-Month Concatenate	Month End	Date to use
2505	18/03/21	5	1	25053		18/03/21
2505	18/03/21	6	2	25053		18/03/21
2505	20/03/21	4	3	25053	Month End	20/03/21
33650	01/10/21	2	4	3365010		01/10/21
33650	21/10/21	33	5	3365010	Month End	21/10/21
20000	12/12/21	10	6	2000012	Month End	12/12/21

The column Month End is really important as I only look at the pallets we have at the end of the month. It helps in understanding our stocks on hand, and I can use it as a filter to get the Date to use.

Index is a simple index - I was able to recreate this column in Power Query

Concatenate - I was able to recreate this column in Power Query

Month End, however is a different task. 

In Excel I'd use = IF(MAXIFS(Table1[Index],Table1[Product-Month Concatenate],Table1[Product-Month Concatenate])=Table1[Index],"Month End","")

Is there any way to M Code my way through this ? I do not want to use the group by feature as I fear I'd lose all the other columns - not present in this sample data.

Thank you very much in advance for the help provided!

Regards, 

Marcel

[Marcel_]

@edhans @mahoneypat 

Hi guys, sorry for the delay.

I have tried both and it worked so fine.. amazing. Thank you so much!

Cheers, 

Marcel

[edhans]

Great @Marcel_ - glad you have a working solution.

[mahoneypat]

This would be done more easily with a DAX measure or column, but here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY1LCsAwCESvUlwHHMcaepeQ+1+j1kA/dHDhvAc6hjAQ0sQOhSttqyQIme1nc+1vTtx8L+7eA9lgaliCH8FHuK9TyFw/qDllDDLnCQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, Date = _t, Pallets = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "aa-DJ"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Pallets", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month End", each let 
thisproduct = [Product ID],
maxdate = List.Max(Table.SelectRows(#"Changed Type", each [Product ID] = thisproduct)[Date])
in
if [Date] =  maxdate then "Month End" else "", type text)
in
    #"Added Custom"

Pat

[edhans]

Try it using the Group By feature. Add a final aggregation that is "All Rows" - this will preserve all of your data and you can re-expand it at the end of the process. If that doesn't work, or you need help with this, let us know.

[Marcel_]

Hi @edhans, 

Thanks. I have tried the Group By feature but I am not really sure I am using it correctly, as I am trying to retrieve the last value at the end of each month.

i.e :

Product ID	Date	Pallets	Index	Product-Month    Concatenate	Month End	Date to use
2505	18/03/21	5	1	25053		18/03/21
2505	18/03/21	6	2	25053		18/03/21
2505	20/03/21	4	3	25053	Month End	20/03/21
33650	01/10/21	2	4	3365010		01/10/21
33650	21/10/21	33	5	3365010	Month End	21/10/21
20000	12/12/21	10	6	2000012	Month End	12/12/21
1000	01/02/22	5	7	10001		01/02/22
1000	01/02/22	5	8	10001		01/02/22
1000	01/02/22	7	9	10001	Month End	01/02/22

Thanks for the All Rows tip!

Regards, 

Marcel

[edhans · Accepted solution]

Here you go @Marcel_

Here is what I did

1. Grouped the data by product ID with two aggregations, one for all rows, one for max of date. That returned the table you see at the top.
   1. 
2. I expanded the table for the date and pallets which gives this:
   1. 
3. I then added a new column that compared Max Date with Date. If it is the same, return "Month End", otherwsie null. = if [Date] = [Max Date] then "Month End" else null
4. THen I cleaned up the columns. Final result:
   1. 

By using the Group By feature, you are partitioning the data per product ID. That will make it perform better over large data sets. On 1,000 records it won't matter, but above that and you'll see performance gains with Group By.

Full code here. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY1LCsAwCESvUlwHHMcaepeQ+1+j1kA/dHDhvAc6hjAQ0sQOhSttqyQIme1nc+1vTtx8L+7eA9lgaliCH8FHuK9TyFw/qDllDDLnCQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, Date = _t, Pallets = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pallets", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-BM"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Product ID"}, {{"All Rows", each _, type table [Product ID=nullable text, Date=nullable date, Pallets=nullable number]}, {"Max Date", each List.Max([Date]), type nullable date}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Pallets"}, {"Date", "Pallets"}),
    #"Added Month End" = Table.AddColumn(#"Expanded All Rows", "Month End", each if [Date] = [Max Date] then "Month End" else null, type text),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Month End",{"Product ID", "Date", "Pallets", "Month End"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Pallets", Int64.Type}})
in
    #"Changed Type1"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.