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
latimeria
3 years agoSolution 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"latimeria
3 years agoSolution 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