I have a table as indicated below where the Category column could have multiple categories each with its own scale which is shown in the Value column. I would like to create a new column that multiplies all the values where the item column does not equal 'scale' by the corresponding scale value that matches its category. In the example below: if Category = A then multiply the value by 10

I've been stuck with this for a while and would truly appreciate any help.

Note - there are hundreds of categories and each one could have a different value. In the actual data, the category is a date and for each of those dates, there is a corresponding scale value that the remainder of the data that matches that date will need to be multiplied by.

Original Table

New Column added

CodePudding user response：

Assuming your first table is named Table1, create a new table, Table2, via

let
    Source = Table1,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Item] = "Scale"))
in
    #"Filtered Rows"

Then create a third table, via

let
  Source = Table.NestedJoin(
    Table1, 
    {"Category"}, 
    Table2, 
    {"Category"}, 
    "Table2", 
    JoinKind.LeftOuter
  ), 
  #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Value"}, {"Table2.Value"}), 
  #"Added Custom" = Table.AddColumn(
    #"Expanded Table2", 
    "Custom", 
    each if [Item] = "Scale" then null else [Value] * [Table2.Value]
  )
in
  #"Added Custom"

CodePudding user response：

Have a lookup table with each category and the matching multiplication factor, then use the following formula:

=C2*INDEX(H$2:H$5,MATCH(A2,G$2:G$5,0))