Forum Discussion

pescadicto's avatar
pescadicto
Icon for Helper I rankHelper I
4 years ago

How to scale a multi-categories / multiple vales using another table with factors

Hi! I need some help:   I want to scale the first table using the factors of the second table to get the third table: The indexes in the first table are repeated (you can think of them as ca...
Need Help
v-yanjiang-msft's avatar
v-yanjiang-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi pescadicto ,

According to your description, here's my solution.

1. In Power Query, select Home> Merge Queries> Merge Queries as New.

2.Expand the FACTORS table exclude INDEX column.

3. Add custom columns.

SCALED1 = Value.Multiply([VALUE 1],[FACTORS.FACTOR 1])
SCALED2 = Value.Multiply([VALUE 2],[FACTORS.FACTOR 2])
.... = Value.Multiply([#"..."],[#"FACTORS...."])
SCALED n = Value.Multiply([VALUE n],[FACTORS.FACTOR n])

4. Remove all redundant columns.

 

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • pescadicto's avatar
    pescadicto
    Icon for Helper I rankHelper I
    4 years ago

    Thanks for the answer! It's a good approach to what I need, but unfortunately it is held with hardcoles values.

    Finally I will use this solution.