Forum Discussion

JaipalAnu's avatar
JaipalAnu
Icon for Helper III rankHelper III
2 years ago
Solved

Convert the DAX Query to Power Query to create Custom Column

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


General Comment
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

11 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      JaipalAnu
      Icon for Helper III rankHelper III
      2 years ago

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