Convert DAX to M code

[milomilo2020]

Hi, I have this DAX

VAR _action_id =
    CALCULATE ( SELECTEDVALUE ( 'base fact_ds_action'[action_id] ) )
VAR _insertion_date =
    CALCULATE ( SELECTEDVALUE ( 'base fact_ds_action'[insertion_date] ) )
VAR _ds_action_sk_finish =
    CALCULATE (
        MIN ( 'base fact_ds_action'[insertion_date] ),
        FILTER (
            ALL ( 'base fact_ds_action' ),
            'base fact_ds_action'[action_id] = _action_id
                && 'base fact_ds_action'[insertion_date] > _insertion_date
        )
    )
RETURN
    IF ( ISBLANK ( _ds_action_sk_finish ), TODAY (), _ds_action_sk_finish - 1 )

What would be the best approach to do the same logic on M code?

Please see attached CSV with Expected result

https://drive.google.com/file/d/16K8CjMfNRv1CXpMcZZQ5HKtA_dw6G4c4/view?usp=sharing 

Logic:

There are repeated ID's with different dates. I need to retrieve the latest date. If the Date is the latest, then I need to have TODAY()

For example, ID = 1200

It appears in two rows. In the first one, 3/1/2022, the expected result is 8/1/2022, because there is another row with ID 1200 and latest date. On the other hand, the row with ID = 1200 and Date = 8/1/2022 has TODAY(), because there is no other newest date for that ID.

Thanks!

[Anonymous · Accepted solution]

Hi @milomilo2020 ,

Accoding to my understanding, you want to set Today for the row with the max date of each ID, the remains set the max date.

Please create a new blank query and paste the following M to the Advanced Editor dialog:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIyMDLSN9Q3VIrVAYoZIYsZQ8RMTDHVoeg1waLXAoteiDpLIECIGWERM8MiZmgAFrQAAlTBWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count", each _, type table [ID=nullable number, Date=nullable date]}, {"Max", each List.Max([Date]), type nullable date}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date"}, {"Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each if [Date]=[Max] then Date.From( DateTime.LocalNow()) else [Max]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Max"})
in
    #"Removed Columns"

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.