Forum Discussion
Administratorhow to pull out and recombine data in powerquery
We have a CVS file that contains investment portfolio data from multiple accounts. The main issue is that some accounts have individual fixed income entries (bonds, CDs, etc.) as well as stock and bond fund entries. This is in a CVS file. We do have another CVS file that has only the fixed income holdings. It's easy to create total portfolio value by account but we don't want all the individual fixed income holdings in the list.
We need to pull out the individual fixed income entries, sum them and add them back as one "Fixed Income" entry (associated with the original investment account) to show the total value of each account.
Hi Syndicate_Admin ,
Power Query is used as a data transformation tool which could achieve it in Power Query Editor.
Based on your description, sounds like you want to calculate some row total for each acount if possible.
If so you can try like this query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MACSpiDCGMiM1YlWcgKyjQyg4iDSyBQs7gxTaAQWNQRJxsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, V1 = _t, V2 = _t, V3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"V1", Int64.Type}, {"V2", Int64.Type}, {"V3", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Income entires", each List.Sum({[V1],[V2],[V3]}),Int64.Type) in #"Added Custom"If not, you can consider providing some sample data and your expected output for this thread.
Sample data and expected output would help tremendously.
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/38490Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- serpiva64
Solution Sage
Hi,
in power query it is certainly possible but without sample data it is not easy to create a model
v-yingjlCommunity Support
Hi Syndicate_Admin ,
Power Query is used as a data transformation tool which could achieve it in Power Query Editor.
Based on your description, sounds like you want to calculate some row total for each acount if possible.
If so you can try like this query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MACSpiDCGMiM1YlWcgKyjQyg4iDSyBQs7gxTaAQWNQRJxsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, V1 = _t, V2 = _t, V3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"V1", Int64.Type}, {"V2", Int64.Type}, {"V3", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Income entires", each List.Sum({[V1],[V2],[V3]}),Int64.Type) in #"Added Custom"If not, you can consider providing some sample data and your expected output for this thread.
Sample data and expected output would help tremendously.
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/38490Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- serpiva64
Hi,
is there a reason why did you use List.Sum instead of something like this
= Table.AddColumn(#"Added Custom", "Custom", each +[V1]+[V2]+[V3])
Thank'you very much
