Cost Analysis in Percentage with Data from Different Tables and Drill Down

[KoroneL]

Hi everyone,

I'm working on a cost analysis where I need to calculate values as percentages. However, my data is spread across two tables: a main table and a secondary table. Additionally, I need to replace some rows in my main table using data from the drill down available in the secondary table.

To clarify my issue, here is a simple example with sample data and a final table with the result i want to get. My goal is to create all measures from the final table as it looks much more simple than working other way around. 

I've been trying to sort things out with merge table but can't solve the fact that the columns don't merge. Any ideas? 

Here are some sample data of the two original table and the one i seek to produce.

Thanks.

Main table - portfolio
Asset	Type	Value
alpha,inc	equity	$                 100,00
Beta AG	bond	$                 200,00
C Main	OIC	$                    75,00	*
Delta SE	equity	$                 150,00
Gama De	bond	$                 110,00
F Main	OIC	$                    80,00	**
secondary table - OICPortfolio
OIC	Asset	Type	Value
C Main	Phi, SA	equity	$ 55 000,00
C Main	Omega SE	bond	$   5 000,00
C Main	Nu Inc	equity	$ 90 000,00
C Main	alpha,inc	equity	$ 54 500,00
F Main	Kappa BV	equity	$ 97 000,00
F Main	Zeta Corp	bond	$ 69 000,00
F Main	Lambda ES	equity	$ 10 000,00
F Main	Theta SW	equity	$   9 000,00
example of "new" or "renewed" table
Asset	Type	Value
alpha,inc	equity	$                 100,00
Beta AG	bond	$                 200,00
Phi, SA	equity	$                    20,17	*
Omega SE	bond	$                      1,83	*
Nu Inc	equity	$                    33,01	*
alpha,inc	equity	$                    19,99	*
Delta SE	equity	$                 150,00
Gama De	bond	$                 110,00
Kappa BV	equity	$                    41,95	**
Zeta Corp	bond	$                    29,84	**
Lambda ES	equity	$                      4,32	**
Theta SW	equity	$                      3,89	**

[Omid_Motamedise · Accepted solution]

Thanks for sharing such a lovely question

please find the solution as bellow.

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSswpyEjUycxLVtJRSi0szSypBDIMDQyUYnWilZxSSxIVHN2BIkn5eSlAyggq4azgm5iZBxTw93QGkuamYFGX1Byg+mBXFKNMITrcE3MTFVxSEUYZGkIk3FCNsgCKxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Asset = _t, Type = _t, Value = _t]),
    CHT1 = Table.TransformColumnTypes(Table1,{{"Value", Int64.Type}}),
    Table2= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclbwTczMU9JRCsjI1FEIdgSyUgtLM0sqgQxTUwMDA6VYHSRV/rmp6YkKwa5AZlJ+XgpIEYYav1IFz7xkZIMsDTAUJeYUZCTqZKKqMzUxhapzg6nzTiwoSFRwCkMxztwAXVlUakmignN+UQHCYWaWGKp8EnOTUhIVXIORTTM0wFAXkgEyLjgc3Q9KsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OIC = _t, Asset = _t, Type = _t, Value = _t]),
    CHT2 = Table.TransformColumnTypes(Table2,{{"Value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(CHT1, {"Asset"}, CHT2, {"OIC"}, "Table2", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Table2 modified", each if Table.IsEmpty([Table2]) then Table.FromRecords({_}) else Table.TransformColumns([Table2],{"Value",(x)=>x*[Value]/List.Sum([Table2][Value])})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Table2 modified"}),
    #"Expanded Table2 modified" = Table.ExpandTableColumn(#"Removed Other Columns", "Table2 modified", {"Asset", "Type", "Value"}, {"Asset", "Type", "Value"})
in
    #"Expanded Table2 modified"

[dufoq3]

Hi @KoroneL, check this:

I don't know how did you calculate [Value] in your sample output, but you should be albe to do it with output below

Output

let
    MainTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSswpyEjUycxLVtJRSi0szSypBDIUVBTQgaGBgY6BgVKsTrSSU2pJooKjO1BdUn5eCnblRgjlzgq+iZl5QGX+ns7YFQOBuSlMuUtqDtD4YFdC7jGFW+CemJuo4JKK1z2GhnDlbsS4xwKiPBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Asset = _t, Type = _t, Value = _t]),
    SecondaryTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc69CsIwGIXhWzkExwwRjNJRSwXxFyIKlg5fbbAB20ZpB+/eimhrzHaGh5cTxyzEmkzJONvlhkNN26Vvjakf7cAAUkIIwYVgCe/hbaEvBBW1M63K7G0BP940WJRnJxwIr6WrzYmbPy5HkB2ff/iSrCXMDm580o9/9UnXhLC625/b48CLV1SkGSFSTnsovHyfv+Lq6GigqydP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OIC = _t, Asset = _t, Type = _t, Value = _t]),
    MainTableChangedType = Table.TransformColumns(MainTable,{{"Value", each Currency.From(Text.Remove(_, " "), "en-US"), Currency.Type}}),
    SecondaryTableChangedType = Table.TransformColumns(SecondaryTable,{{"Value", each Currency.From(Text.Remove(_, " "), "en-US"), Currency.Type}}),
    MergedQueries = Table.NestedJoin(MainTableChangedType, {"Asset"}, SecondaryTableChangedType, {"OIC"}, "SecondaryTableChangedType", JoinKind.LeftOuter),
    // Necessary if you want to preserve sort order.
    AddedIndex = Table.AddIndexColumn(MergedQueries, "Index", 0, 1, Int64.Type),
    ExpandedSecondaryTableChangedType = Table.ExpandTableColumn(AddedIndex, "SecondaryTableChangedType", {"Asset", "Value"}, {"ST Asset", "ST Value"}),
    ReplacedAsset = Value.ReplaceType(Table.ReplaceValue(ExpandedSecondaryTableChangedType,
        each [St Asset] <> null,
        each [ST Asset],
        (x,y,z)=> if y then z else x,
        {"Asset"} ), Value.Type(Table.FirstN(ExpandedSecondaryTableChangedType, 0))),
    RemovedColumns = Table.RemoveColumns(ReplacedAsset,{"ST Asset", "Index"})
in
    RemovedColumns