Forum Discussion
Vinod_P
3 years agoHelper I
How to multiply one reference column with multiple columns
Want to multiply prep hours with each month and form a new table .
Please provide solution
2 Replies
- latimeriaSolution Specialist
Hi Vinod_P ,
Is it what you're looking for:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s1PSc1R0lEKKEotUMjILy0qBnK8EvOApFtqEpD0TSwCko4FRWB2JUi2NA9MgrQ5lqYDyeDUAiDpn1wCJP3yy4CkS2qyUqxOtJKJgTGQZwjERlDaAA2D5cywEiADjA1MTZxxaMWGEepAupPz9UyBHGMdUyK1WQKxBRhDtZtjsRuDj52IjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Model", type text}, {"Prep hours", type number}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"May", Int64.Type}, {"Jun", Int64.Type}, {"Jul", Int64.Type}, {"Aug", Int64.Type}, {"Sep", Int64.Type}, {"Oct", Int64.Type}, {"Nov", Int64.Type}, {"Dec", Int64.Type}}), #"Added Custom" = Table.AddColumn( #"Changed Type", "Custom", each List.Sum( Record.ToList( Record.SelectFields(_, List.RemoveFirstN( Table.ColumnNames(#"Changed Type"),2) ) ) ) * [Prep hours] ) in #"Added Custom"- latimeriaSolution Specialist
shorter version
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s1PSc1R0lEKKEotUMjILy0qBnK8EvOApFtqEpD0TSwCko4FRWB2JUi2NA9MgrQ5lqYDyeDUAiDpn1wCJP3yy4CkS2qyUqxOtJKJgTGQZwjERlDaAA2D5cywEiADjA1MTZxxaMWGEepAupPz9UyBHGMdUyK1WQKxBRhDtZtjsRuDj52IjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Model", type text}, {"Prep hours", type number}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"May", Int64.Type}, {"Jun", Int64.Type}, {"Jul", Int64.Type}, {"Aug", Int64.Type}, {"Sep", Int64.Type}, {"Oct", Int64.Type}, {"Nov", Int64.Type}, {"Dec", Int64.Type}}), #"Added Custom" = Table.AddColumn( #"Changed Type", "Custom", each List.Sum(List.Skip(Record.ToList(_),2)) * [Prep hours]) in #"Added Custom"Thanks Vijay_A_Verma