Need help to convert dax query into M Query

[Anonymous]

Hi Team,  @tamerj1

Thanks for helping in the below mentioned question.

Solved: Re: Need Help with DAX Command - Microsoft Power BI Community

below is the formula which is working fine .

CYCQCW-Win Rev =
VAR CurrentFCST = MAX ( 'Trend'[FCST] )
VAR CurrentWeek = MAX ( 'Trend'[WEEK_NUM] )
VAR CurrentStage = MAX ( 'Trend'[SS_No])
VAR CurrentValue = MAX ( 'Trend'[REV$M] )
VAR MaxWeek = CALCULATE ( MAX ( 'Trend'[WEEK_NUM] ), ALLEXCEPT ( 'Trend', 'Trend'[FCST] ) )
RETURN
IF (
CurrentFCST = "CQ" && CurrentWeek = MaxWeek && CurrentStage IN { "07", "08" },
CurrentValue,
0
);

As per my requirement i have to create a chart which shows the data in dynamic hierarchy , and as this formula is written in dax i cannot view this column in editor , can you please help me in changing it to M.
Thanks & Regards
Shiv Kumar

[sujana_pbi]

Hello, could anyone help me with my task? 

below is one how-to do in m query.

VAR Maxval =

    MAX ( Sheet1[length] )

VAR IND = Sheet1[Index] + 1

VAR EXPN =

    LOOKUPVALUE ( Sheet1[length], Sheet1[Index], Sheet1[Index] + 1 )

VAR FNL =

    Sheet1[length]

        - LOOKUPVALUE ( Sheet1[length], Sheet1[Index], Sheet1[Index] - 1 )

VAR FNL1 =

    Sheet1[length]

        - LOOKUPVALUE ( Sheet1[length], Sheet1[Index], Sheet1[Index] + 1 )

RETURN

    IF (

        Sheet1[length] = Maxval

            || (FNL < 0 && Sheet1[length]<> 0)

            || ISBLANK ( EXPN )

            || FNL1 = 0 && Sheet1[length] in {5,10,15,20,25,30}

            &&(FNL1=0 )

            ||(FNL=0&&EXPN=0),1,0)

[tamerj1 · Accepted solution]

Hi @Anonymous 

Here you go

let
    Source = Excel.Workbook(File.Contents("C:\Users\Tamer\Dropbox\Tamer - Personal\Work\PBI Community\Community 2\Community 112.xlsx"), null, true),
    Trend_Sheet = Source{[Item="Trend",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Trend_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"FCST", type text}, {"WEEK_NUM", Int64.Type}, {"SS_No", Int64.Type}, {"REV$M", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "NEW REV$M", each if [FCST] = "CQ" and ( [SS_No] = 7 or [SS_No] = 8 ) and [WEEK_NUM] = List.Max (Table.SelectColumns(Table.SelectRows(#"Changed Type", each ([FCST] = "CQ")), {"WEEK_NUM"} )[WEEK_NUM]) then [#"REV$M"] else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"NEW REV$M", type number}})
in
    #"Changed Type1"