Forum Discussion
Tomek1982
2 years agoHelper I
table merging
How to merge such two tables: to get such a result (value is divided by shares and bonds percentage)? pbix is here if needed.
- 2 years ago
Ahmedx
2 years agoSuper User
Hi pls try this code
let
Source = #"funds structure",
#"Replaced Value" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,{"shares", "bonds"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"funds"}, "Attribute", "Value"),
#"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"funds"}, #"funds value", {"funds"}, "funds value", JoinKind.LeftOuter),
#"Expanded funds value" = Table.ExpandTableColumn(#"Merged Queries", "funds value", {"value"}, {"value.1"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded funds value",{{"value.1", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "NewValue", each [Value]*[value.1]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value", "value.1"})
in
#"Removed Columns"