Forum Discussion
PowerBI custom column
I am trying to create a column that has a sum value of column Amort Cost "AC" by Date "DA, and by Acccount Number"AN". How am I able to do this? Would a new measure be easier? Appreciate any feed back. Thanks
Hi,
Greg_Deckler , Thanks for your concern about the problem, and i want to offer some more infotmation for user to refer to.
hello mdomin35 , you can use the following solutions to implement it.
1.Use group by.
Output
2.You can add a custom column
List.Sum(Table.SelectRows(#"Changed Type"(your last step name),(x)=>x[AN]=[AN] and x[DA]=[DA])[AC])Output
And you can put the following code to advanced editor in power query to refer to.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMDLRN9Q3BDINDZRidVBEjcBMdFGQWmMMUVMg0wQimoSq1hRDFGSuGVa15hiiIHMtIKLJqGotMUSNEL5AU2uEIWoK9UUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AN = _t, DA = _t, AC = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"AN", type text}, {"DA", type date}, {"AC", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Sum(Table.SelectRows(#"Changed Type",(x)=>x[AN]=[AN] and x[DA]=[DA])[AC])) in #"Added Custom"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.
2 Replies
mdomin35 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hi,
Greg_Deckler , Thanks for your concern about the problem, and i want to offer some more infotmation for user to refer to.
hello mdomin35 , you can use the following solutions to implement it.
1.Use group by.
Output
2.You can add a custom column
List.Sum(Table.SelectRows(#"Changed Type"(your last step name),(x)=>x[AN]=[AN] and x[DA]=[DA])[AC])Output
And you can put the following code to advanced editor in power query to refer to.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMDLRN9Q3BDINDZRidVBEjcBMdFGQWmMMUVMg0wQimoSq1hRDFGSuGVa15hiiIHMtIKLJqGotMUSNEL5AU2uEIWoK9UUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AN = _t, DA = _t, AC = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"AN", type text}, {"DA", type date}, {"AC", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Sum(Table.SelectRows(#"Changed Type",(x)=>x[AN]=[AN] and x[DA]=[DA])[AC])) in #"Added Custom"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.
