Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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])))
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!