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

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.

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

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.