Hi everyone. Could anyone help me with this Power query problem

user_17461139 0
Viewed 60

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

Item Category Value
Scale A 10
Scale B 5
Scale C 2
Apples A 100
Fruit B 10
Car C 15
Pear A 20
Lemon B 5

New Column added

Item Category Value Calc_Val
Scale A 10 null
Scale B 5 null
Scale C 2 null
Apples A 100 1000
Fruit B 10 50
Car C 15 30
Pear A 20 200
Lemon B 5 25
powerquery excel
2 Answers

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

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

enter image description here

user_82604840
Related