how to pull out and recombine data in powerquery

[Syndicate_Admin]

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.

[v-yingjl · Accepted solution]

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/38490

Best 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

[serpiva64]

Hi,

in power query it is certainly possible but without sample data it is not easy to create a model