Forum Discussion

M_SBS_6's avatar
M_SBS_6
Icon for Helper V rankHelper V
2 years ago

DAX to M

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
Need Help

2 Replies

  • _AAndrade's avatar
    _AAndrade
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    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
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.