Convert the DAX Query to Power Query to create Custom Column

[JaipalAnu]

Hi Everyone,

Please Help me to Convert the DAX Query to Power Query to create Custom Column. Below is the Query.

The below Dax Query is working well as expected in the Desktop, for few reports the column is needed from power query. So I'm trying to convert it in Power Query but getting error. Pasting the Power query also which i tried.

Dax Query
------------

YTD=

VAR selectMonth = MAX('CostPlans_Monthly'[Reporting_MonthNumber])

RETURN

CALCULATE(

    SUM('CostPlans_Monthly'[Value]),

    FILTER(

        ALL('CostPlans_Monthly'),

        'CostPlans_Monthly'[CostPlanName] = SELECTEDVALUE('CostPlans_Monthly'[CostPlanName]) &&

        'CostPlans_Monthly'[Reporting_MonthNumber] <= selectMonth &&

        ('CostPlans_Monthly'[PlanType] = "Actual" || 'CostPlans_Monthly'[PlanType] = "Plan")

    )

)

Power Query
----------------

let
Source = CostPlans_Monthly2, 
SelectMonth = List.Max(Source[Reporting_MonthNumber]),
FilteredTable = Table.SelectRows(
Source,
each [CostPlanName] = Table.SingleValue(Source[CostPlanName])
and [Reporting_MonthNumber] <= SelectMonth
and ([PlanType] = "Actual" or [PlanType] = "Plan")
),
YTDTotal = List.Sum(FilteredTable[Value])
in
YTDTotal


Please help me to fix this

[wdx223_Daniel]

let
Source = CostPlans_Monthly2, 

Custom1=Table.Group(

                                     CostPlans_Monthly2,

                                     "CostPlanName",

                                     {"n",each let a=Table.Group(

                                                                                  _,

                                                                                 { "CostPlanName","Reporting_MonthNumber"},

                                                                                 {"MonthTotal",each List.Sum(Table.SelectRows(_,each [PlanType]="Actual" or [PlanType]="Plan")[Value])}

                                                                                  ),

                                                           b=List.Accumulate(a[MonthTotal],{},(x,y)=>x&{List.Last(x,0)+y})

                                                    in Table.FromColumns(Table.ToColumns(a)&{b},Table.ColumnNames(a)&{"YTD"})

                                       }

                                     ),

Custom2=Table.Combine(Custom1[n])

in

Custom2

[JaipalAnu]

Thanks for your reply, I have tried with the Query you provided but getting error.

[wdx223_Daniel]

what's the error?

[JaipalAnu]

After Custom Column Created getting error like below. Attached Image.

[wdx223_Daniel]

what's the message in the error?

[JaipalAnu]

There is no message, The error is showing for all the rows after custom column created.

[wdx223_Daniel]

you can select any cell, then the error message will be show under the table

[JaipalAnu]

The error is below

[wdx223_Daniel]

try this code

Source = Table.Buffer(CostPlans_Monthly2), 

Custom1=Table.Group(

                                     Source,

                                     "CostPlanName",

                                     {"n",each let a=Table.Group(

                                                                                  _,

                                                                                 { "CostPlanName","Reporting_MonthNumber"},

                                                                                 {"MonthTotal",each List.Sum(Table.SelectRows(_,each [PlanType]="Actual" or [PlanType]="Plan")[Value])}

                                                                                  ),

                                                           b=List.Accumulate(a[MonthTotal],{},(x,y)=>x&{List.Last(x,0)+y})

                                                    in Table.FromColumns(Table.ToColumns(a)&{b},Table.ColumnNames(a)&{"YTD"})

                                       }

                                     ),

Custom2=Table.Combine(Custom1[n])

in

Custom2

[JaipalAnu]

No Luck, Same error. 

Anyhow, Thanks for your time.

[v-xinruzhu-msft · Accepted solution]

Hi @JaipalAnu 

You can create a blank query and put the following code to advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJMLilNzAEyjAyMjPUN9Q2BTEMDpVgdiHRATmIeTNJI3xTMxCppDNZpjJD0L8lILUKVNYHIOmE11xAhieomiF4j7HpRrHVCt9YI2VpnNK0ofnXGsBbFt+h6EdbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CostPlanName = _t, PlanType = _t, Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CostPlanName", type text}, {"PlanType", type text}, {"Date", type date}, {"Value", Int64.Type}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Reporting_MonthNumber", each Date.Month([Date]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Month", "Sum_value", each List.Sum(Table.SelectRows(#"Inserted Month",(x)=>(x[PlanType]="Actual" or x[PlanType]="Plan") and x[CostPlanName]=[CostPlanName] and x[Year]=[Year] and x[Reporting_MonthNumber]<=[Reporting_MonthNumber])[Value])),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"CostPlanName", Order.Ascending}, {"Date", Order.Ascending}})
in
    #"Sorted Rows"

Best Regards!

Yolo Zhu

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