Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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.



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


            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])))

Super User
Super User

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


Input data:



M code:


    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}})
    #"Sorted Rows1"


I added some steps for your clarity...





Hope this helps!


Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors