Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
------------
Solved! Go to 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.
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
Thanks for your reply, I have tried with the Query you provided but getting error.
what's the error?
After Custom Column Created getting error like below. Attached Image.
what's the message in the error?
There is no message, The error is showing for all the rows after custom column created.
you can select any cell, then the error message will be show under the table
The error is below
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
No Luck, Same error.
Anyhow, Thanks for your time.
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.