Value.Multiply function in Power Query

[Chris646]

Hi I am having a lot of trouble using the Value.Multiply function in my power query. In the attached example I have a power query table with Projected Revenue in EUR. In the same power query I am trying to add a custom column that multiplies the results of the projected Revenue column by a single user input value shown on the worksheet "conversion rate". I have tried multiple formulas and am having no luck like this one:
Value.Multiply([Projection Revenue EUR],Excel.CurrentWorkbook(){[Conversion Rate]}[Content]{0}[Column1])
I would think this would be pretty easy? Any thoughts?

Thanks-

 

[Ken Puls]

Hi Chris,

TO make this work, I would suggest you flatten the table first. Essentially, you want to pull the rate from your exchange table into a column of the revenue table so that your values show on each row. At that point it's really easy.

I've set up a quick example here using a cartesian product to join the tables. Do note that this only works as you have a single value, if there were more in the currency conversion table, you'd need to go with some kind of join instead.

 

[Ron Coderre]

You indicated that there would be one user conversion rate input.
So....I named that cell rngConversion.
Then...I used this M-Code:

let
// Get the Conversion Factor
    ConvFactor = Excel.CurrentWorkbook(){[Name="rngConversion"]}[Content]{0}[Column1],
    
// Connect to Table1
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
// Add a column that multiplies each table value by the conversion factor
AddedConvertedCol = Table.AddColumn(Source, "Custom", each [Projection Revenue EUR] * ConvFactor)
in
    AddedConvertedCol

Is that something you can work with?

 

[Chris646]

I get a Formula.Firewall error using this method: Query 'Combine ECO Models' (step 'Added Custom') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

[Chris646]

So in my real spreadsheet, I am struggling to see how to put this code in my existing query. I copied everything currently in my power query below and highlighted the line where I am trying to insert this multiplication. Can you show me how your M-code would be inserted in the below?

let
Source = Folder.Files("S:\share14\Technology Operations Engineering (TOE)\Project Management Monthly Input\PMMR Current\Final\Single PSL Models\Ecoplanning"),
#"Removed Columns" = Table.RemoveColumns(Source,{"Content", "Extension", "Date accessed", "Attributes"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each Text.Combine({Text.Middle(Text.From([Name]),Text.PositionOf(Text.From([Name]),"_")+1,4),"ECO"},"_")),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Custom", "Name", "Date modified", "Date created", "Folder Path"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Project #"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each fnECO([Folder Path]&[Name])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Project #"}),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Errors",{"Folder Path"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Projection Date", "Projection Revenue", "Projection Cost", "Projection Income", "Projection Hours", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91"}, {"Projection Date", "Projection Revenue", "Projection Cost", "Projection Income", "Projection Hours", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91"}),
#"Removed Errors1" = Table.RemoveRowsWithErrors(#"Expanded Custom", {"Project #"}),
#"Added Custom2" = Table.AddColumn(#"Removed Errors1", "Custom", each [Projection Revenue]*ConvRate),
Custom = #"Added Custom2"{0}[Custom]
in
Custom