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

[pescadicto]

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

How can I get the third table using only the Power Query Editor of POWER BI Desktop?

This is the pbi

Thank you!

[v-yanjiang-msft]

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]

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.

[lbendlin]

Attached is a brute force method that uses an additional index on the TABLE table.

[pescadicto]

Thanks for the answer! It's a good approach to what I need, but unfortunately it' a brute force method.

Finally I will use this solution.

[lbendlin]

I hope you are aware that what they proposed on SO is pretty much identical to my proposal. Both are brute force and may not work at larger scales.

[pescadicto]

Hi @lbendlin! I think (I am a beginner with PBI) that the solution of SO does not use brute force.

That solution requires that the names of the corresponding columns in the Values and Factors tables be the same (since in my example they are not the same, SO adds an extra step by replacing the words "value" and "factor". I applied that solution to tables with numerous columns and it works, regardless of the column names, as long as the corresponding column names in the Values and Factors tables are the same.Because of that (multiple columns with unstructured names) I think the SO solution doesn't use brute force. Anyway, I appreciate your solution and the time you spent helping me.
Greetings,

José

[pescadicto]

@lbendlin you right, I founded the brute force in the table FACTORS. Thanks.

[lbendlin]

here is some inspiration if you want to investigate further

Excel Matrix Multiplication - Replacing MMULT with Power Query - DataChant

It's a bit overkill but can serve as guidance.

[lbendlin]

@pescadicto your factors table is missing the reference for index 50.

"The indexes in the first table are repeated" - that's not how an index should be used. You may need to add a true index to the TABLE table.

@ImkeF  How can I know which column I am currently in?  This is how far I got 

let
    Source = TABLE,
    CN = List.RemoveFirstN(Table.ColumnNames(Source),1),
     #"Merged Queries" = Table.NestedJoin(Source, {"INDEX"}, FACTORS, {"INDEX"}, "FACTORS", JoinKind.LeftOuter),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Queries", null,each Table.Column([FACTORS],"FACTOR 2"){0},(o,c,n)=>  o*n ,CN),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"FACTORS"}),
   Rename = Table.TransformColumnNames(#"Removed Columns",(k)=>Text.Replace(k,"VALUE","SCALED"))
in
    Rename

but you can see that I hardcoded "FACTOR 2".  I would need to know that I am in the "VALUE 1" column and then I would replace that column name with "FACTOR 1" for the value lookup.

I don't think Power Query supports matrix operations like "multiply table a with table b", right?

pbix is attached.

[pescadicto]

Thanks for the answer! It's the best approach to what I need: a solution without hardcoded values.

Finally I will use this solution.