Forum Discussion

Ctq17922's avatar
Ctq17922
New Member
3 years ago

Convert DAX to M Code Help!

i am trying to convert the following DAX to m code in power query. it looks at a row in the table and if the work orer column is the same number returns the last staqtus change date. help would be appreciated.

 

CALCULATE(

    MAX('Maximo Work Order Status'[Changed Date]),

        FILTER(

            ALLEXCEPT('Maximo Work Order Status', 'Maximo Work Order Status'[Work Order Number]),

            'Maximo Work Order Status'[Changed Date]<EARLIER('Maximo Work Order Status'[Changed Date])))

Need Help

1 Reply

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    3 years ago

    Check this ... I used some random sample data to get the previous max date row.

     

    Input data:

     

    M code:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc7BDcAgDAPAXfJGcmxKW2ZB2X8NoI/2kfp5smSPYVyxYt7gJ+SSRXmVAuunWtmK48dasgtiQu+Q0tAN9oT701OtFjEB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Number" = _t, #"Changed Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Changed Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order Number"}, {{"c dates list", each _[Changed Date], type list}, {"aa", each _, type table [Order Number=nullable text, Changed Date=nullable text]}}),
    #"Merged previous Dates Per Group" = Table.NestedJoin(#"Grouped Rows", {"Order Number"}, #"Changed Type", {"Order Number"}, "Previous Calendar Date", JoinKind.LeftOuter),
    #"Expanded Previous Calendar Date" = Table.ExpandTableColumn(#"Merged previous Dates Per Group", "Previous Calendar Date", {"Order Number", "Changed Date"}, {"Order Number.1", "Changed Date.1"}),
    AddPreviousOrder = Table.AddColumn (#"Expanded Previous Calendar Date", "Previous Order Date", each let bDate = [Changed Date.1] in List.Max(List.Select([c dates list], each _ < bDate)), type date),
    #"Removed Columns" = Table.RemoveColumns(AddPreviousOrder,{"c dates list", "aa", "Order Number.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Changed Date.1", "Changed Date"}}),
    #"Sorted Rows1" = Table.Sort(#"Renamed Columns",{{"Order Number", Order.Ascending}, {"Changed Date", Order.Ascending}})
in
    #"Sorted Rows1"

     

    I added some steps for your clarity...

     

    Output:

     

    Hope this helps!