Multiply multiple columns by factor from another table in ideally one step?

[Hambach]

Hi, I have two tables and I would like to multiply several income columns (more than 20 in the real table, ideally all in one step) form the first one by a factor from another table:

Project ID	Losses	Income 1	Income 2
1	3	4	5
2	2	1	2

Project ID	Factor
1	2
2	3

So you get:

Project ID	Losses	Income 1	Income 2
1	3	8	10
2	2	3	6

Since in the real table it will be a lot of columns, is there a convenient formula that will do it in one or just a few steps, directly transforming the existing columns? So I don't have to to the steps of "Create new calculated column, delete the old one, rename the new one" for every single column.

Thank you!

[wdx223_Daniel · Accepted solution]

NewStep=Table.ReplaceValue(Table1,each [Project ID],"",(x,y,z)=>x*Table2{[Project ID=y]}?[Factor]? ??x,List.Select(Table.ColumnNames(Table1),each Text.StartsWith(_,"Income")))

[Anonymous · Accepted solution]

Hi @Hambach 

Here is another method for your reference. Use Unpivot/Pivot columns and Merge queries. 

Unpivot columns - Power Query | Microsoft Learn

Pivot columns feature description - Power Query | Microsoft Learn

Merge queries overview - Power Query | Microsoft Learn

Table 1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIGYhMgNlWK1YlWMgKyQNgQTMfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, Losses = _t, #"Income 1" = _t, #"Income 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", Int64.Type}, {"Losses", Int64.Type}, {"Income 1", Int64.Type}, {"Income 2", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project ID"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Project ID"}, FactorTable, {"Project ID"}, "FactorTable", JoinKind.LeftOuter),
    #"Expanded FactorTable" = Table.ExpandTableColumn(#"Merged Queries", "FactorTable", {"Factor"}, {"Factor"}),
    #"Added Custom" = Table.AddColumn(#"Expanded FactorTable", "NewValue", each if Text.StartsWith([Attribute], "Income") then [Value]*[Factor] else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value", "Factor"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "NewValue")
in
    #"Pivoted Column"

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[dufoq3 · Accepted solution]

Hi @Hambach,

Result:

let
    DataTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIGYhMgNlWK1YlWMgKyQNgQTMfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, Losses = _t, #"Income 1" = _t, #"Income 2" = _t]),
    FactorTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJSitWJBpI6SsZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, Factor = _t]),
    ChangedTypeDataTable = Table.TransformColumnTypes(DataTable,{{"Project ID", Int64.Type}, {"Losses", type number}, {"Income 1", type number}, {"Income 2", type number}}),
    ChangedTypeFactorTable = Table.TransformColumnTypes(FactorTable,{{"Project ID", Int64.Type}, {"Factor", Int64.Type}}),
    MergedQueries = Table.NestedJoin(ChangedTypeDataTable, {"Project ID"}, ChangedTypeFactorTable, {"Project ID"}, "FactorTable", JoinKind.LeftOuter),
    IncomeMultipliedByFactor = Table.ReplaceValue(MergedQueries,
        null,
        each [FactorTable][Factor]{0}?,
        (x,y,z)=> if z = null then x else x*z,
        List.Select(Table.ColumnNames(MergedQueries), each Text.Contains(_, "Income", Comparer.OrdinalIgnoreCase)) ),
    RemovedColumns = Table.RemoveColumns(IncomeMultipliedByFactor,{"FactorTable"}),
    RestoreTypes = Value.ReplaceType(RemovedColumns, Value.Type(ChangedTypeDataTable))
in
    RestoreTypes

[Anonymous · Accepted solution]

Hi @Hambach 

Here is another method for your reference. Use Unpivot/Pivot columns and Merge queries. 

Unpivot columns - Power Query | Microsoft Learn

Pivot columns feature description - Power Query | Microsoft Learn

Merge queries overview - Power Query | Microsoft Learn

Table 1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIGYhMgNlWK1YlWMgKyQNgQTMfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, Losses = _t, #"Income 1" = _t, #"Income 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", Int64.Type}, {"Losses", Int64.Type}, {"Income 1", Int64.Type}, {"Income 2", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project ID"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Project ID"}, FactorTable, {"Project ID"}, "FactorTable", JoinKind.LeftOuter),
    #"Expanded FactorTable" = Table.ExpandTableColumn(#"Merged Queries", "FactorTable", {"Factor"}, {"Factor"}),
    #"Added Custom" = Table.AddColumn(#"Expanded FactorTable", "NewValue", each if Text.StartsWith([Attribute], "Income") then [Value]*[Factor] else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value", "Factor"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "NewValue")
in
    #"Pivoted Column"

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[wdx223_Daniel · Accepted solution]

NewStep=Table.ReplaceValue(Table1,each [Project ID],"",(x,y,z)=>x*Table2{[Project ID=y]}?[Factor]? ??x,List.Select(Table.ColumnNames(Table1),each Text.StartsWith(_,"Income")))