Forum Discussion

youngmasterRD's avatar
youngmasterRD
New Member
3 years ago
Solved

DAX to Power Query

Hello all!   Currently, I have this formula in my DAX.     Status = FILTER(ALL(raw),[Timestamp]=CALCULATE(MAX(raw[Timestamp],ALLEXCEPT(raw,raw[ID])))     How can I have a same outcome in Po...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi youngmasterRD ,

     

    You need to group your raw table on [ID] with the All Rows aggregator, then grab the row that features the MAX of [Timestamp].

     

    Example query code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUTIsLlFwSU1OzU1KLVIwMjACifmllivF6sCVGOWlYCjxzNMNKMpPL0otLkZWalqSgaHUOT+3ICe1JDUFptAIh5lI1oKUGBcRsDYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Timestamp = _t, Status = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Timestamp", type text}, {"Status", type text}}),
    groupId = Table.Group(chgTypes, {"ID"}, {{"data", each _, type table [ID=nullable number, Timestamp=nullable text, Status=nullable text]}}),
    addMaxRecord = Table.AddColumn(groupId, "maxRecord", each Table.Max([data], "Timestamp")),
    expandMaxRecord = Table.ExpandRecordColumn(addMaxRecord, "maxRecord", {"Timestamp", "Status"}, {"Timestamp", "Status"}),
    remOthCols = Table.SelectColumns(expandMaxRecord,{"ID", "Timestamp", "Status"})
in
    remOthCols

     

    Example query output:

     

    Pete

BA_Pete's avatar
BA_Pete
Super User
3 years ago

Hi youngmasterRD ,

 

You need to group your raw table on [ID] with the All Rows aggregator, then grab the row that features the MAX of [Timestamp].

 

Example query code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUTIsLlFwSU1OzU1KLVIwMjACifmllivF6sCVGOWlYCjxzNMNKMpPL0otLkZWalqSgaHUOT+3ICe1JDUFptAIh5lI1oKUGBcRsDYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Timestamp = _t, Status = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Timestamp", type text}, {"Status", type text}}),
    groupId = Table.Group(chgTypes, {"ID"}, {{"data", each _, type table [ID=nullable number, Timestamp=nullable text, Status=nullable text]}}),
    addMaxRecord = Table.AddColumn(groupId, "maxRecord", each Table.Max([data], "Timestamp")),
    expandMaxRecord = Table.ExpandRecordColumn(addMaxRecord, "maxRecord", {"Timestamp", "Status"}, {"Timestamp", "Status"}),
    remOthCols = Table.SelectColumns(expandMaxRecord,{"ID", "Timestamp", "Status"})
in
    remOthCols

 

Example query output:

 

Pete