Forum Discussion

Vinod_P's avatar
Vinod_P
Helper I
3 years ago

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

  • latimeria's avatar
    latimeria
    Solution 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's avatar
      latimeria
      Solution 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