Forum Discussion

Marcel_'s avatar
Marcel_
Frequent Visitor
5 years ago
Solved

Get last Month End Data using Power Query

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   

DatePallets
250518/03/21      5
250518/03/216
250520/03/214
3365001/10/212
3365021/10/2133
2000012/12/2110

 

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               PalletsIndex    Product-Month Concatenate         Month End     Date to use   
250518/03/215125053 18/03/21
250518/03/216225053 18/03/21
250520/03/214325053Month End 20/03/21
3365001/10/21243365010 01/10/21
3365021/10/213353365010Month End21/10/21
2000012/12/211062000012Month End12/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

 

Need Help
  • edhans's avatar
    edhans
    5 years ago

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

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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's avatar
    edhans
    Community Champion
    5 years ago

    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_'s avatar
      Marcel_
      Frequent Visitor
      5 years ago

      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   
      250518/03/215125053 18/03/21
      250518/03/216225053 18/03/21
      250520/03/214325053Month End 20/03/21
      3365001/10/21243365010 01/10/21
      3365021/10/213353365010Month End21/10/21
      2000012/12/211062000012Month End12/12/21
      100001/02/225710001 01/02/22
      100001/02/225810001 01/02/22
      100001/02/227910001Month End01/02/22

       

       

      Thanks for the All Rows tip!

       

      Regards, 

      Marcel

       

      • edhans's avatar
        edhans
        Community Champion
        5 years ago

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

  • Marcel_'s avatar
    Marcel_
    Frequent Visitor
    5 years ago

    edhans mahoneypat 

    Hi guys, sorry for the delay.

     

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

     

    Cheers, 

    Marcel