DAX to M

[M_SBS_6]

Hi,

I have the following DAX as a column buy wondered if this can be translated into M within Power Query at all? 

VAR _max_year = YEAR(MAX('Table'[appdate]))
VAR _min_year = _max_year - 3
VAR _cur_appname = 'Table'[appname]
VAR _count = CALCULATE(COUNTROWS('Table'),'Table'[appname]=_cur_appname && YEAR('Table'[appdate])>=_min_year && YEAR('Table'[appdate])<_max_year)
VAR _result = IF(YEAR('Table'[appdate])=_max_year&&ISBLANK(_count),"Yes")
RETURN
_result

[Anonymous]

Hi @M_SBS_6 ,

Your DAX formula calculates a value based on conditions related to the year of the appdate and the count of rows that meet certain criteria. To achieve a similar outcome in M within Power Query, we'll follow a step-by-step approach. Please note that M operates differently from DAX, and some logic may need to be adjusted to fit the procedural nature of M.
You can try this M function:

let
    Source = YourSourceTable, // Replace YourSourceTable with your actual source step
    _max_year = Date.Year(List.Max(Source[appdate])),
    _min_year = _max_year - 3,
    // Add a custom column to perform the row filtering and counting
    AddCustom = Table.AddColumn(Source, "Custom", each let
        _cur_appname = [appname],
        _filtered = Table.SelectRows(Source, each [appname] = _cur_appname 
            and Date.Year([appdate]) >= _min_year 
            and Date.Year([appdate]) < _max_year),
        _count = Table.RowCount(_filtered)
    in
        if Date.Year([appdate]) = _max_year and _count = 0 then "Yes" else null)
in
    AddCustom

This code is a starting point and may need adjustments based on your specific requirements and data structure. Remember, the M language is case-sensitive and procedural, so the order of operations is crucial.

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

[_AAndrade]

Hi,

Try this code:

let
    _max_year = Date.Year(List.Max(Table[appdate])),
    _min_year = _max_year - 3,
    _cur_appname = Table[appname],
    _count = Table.SelectRows(Table, each [appname] = _cur_appname and Date.Year([appdate]) >= _min_year and Date.Year([appdate]) < _max_year),
    _result = if Date.Year([appdate]) = _max_year and Table.IsEmpty(_count) then "Yes" else null
in
    _result