Forum Discussion

asjones's avatar
asjones
Icon for Helper V rankHelper V
5 years ago
Solved

Max Date Math Issues In Power Query

I wrote an Excel formula that I would like to convert ot Power Querey, but  I cant get the List.Max or Table.Max to work thust the rest fails also. I want a new column that works simular to this exc...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi asjones，

     

    Since you need 2 columns based on the max date,I would suggest you first create a column to get the max date,using below M codes:

    =Table.Max(#"Changed Type","Date")

    Then create another 2 columns to get 7days and 14days:

    =if [Date]>Date.AddDays([Max date.Date],-7) then "7 days" else null
    =if [Date]>Date.AddDays([Max date.Date],-14) then "14 days" else null

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
5 years ago

You can do this asjones - this will work fine on relatively small record sets, thousands of rows. It will not work on tens of thousands or higher.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc31DcyMDJUitUBcYyQOcbIHBNkjikyxwyZY47MsUDmWCJzDA1QeChuMERxhCHMFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = 
        Table.AddColumn(
            #"Changed Type", 
            "Date Comparison", 
            each
                let
                    varDate = [Date]
                in
                if Date.AddDays(List.Max(#"Changed Type"[Date]), -7) < varDate then "7 Days" else "-"
            )
in
    #"Added Custom"

This is what it returns with dates from July 1 - July 13 in my sample data.

 

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.

 

If you need more help, please give us some data to work with with expected results.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.