Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors