Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Hambach
Frequent Visitor

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

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 IDLossesIncome 1Income 2
1345
2212

 

Project IDFactor
12
23

 

So you get:

 

Project IDLossesIncome 1Income 2
13810
2236

 

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!

3 ACCEPTED SOLUTIONS
wdx223_Daniel
Super User
Super User

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")))

View solution in original post

v-jingzhan-msft
Community Support
Community Support

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!

View solution in original post

dufoq3
Super User
Super User

Hi @Hambach,

 

Result:

dufoq3_0-1717050277332.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

3 REPLIES 3
dufoq3
Super User
Super User

Hi @Hambach,

 

Result:

dufoq3_0-1717050277332.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

v-jingzhan-msft
Community Support
Community Support

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
Super User
Super User

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")))

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors