excelpowerquerym

Get data from config table cell when loading data source with power query

I am loading an Excel file. In there, I want to replace values based on the value of another column. The new value shall be pulled from a table on a configuration worksheet of the main Excel file.

Could someone please share the syntax for this, couldn't find anything

let
  ...
  #"DealProbability" = Table.ReplaceValue(#"DealStageIntType", each [Deal probability], 
      each if  [Deal Stage] = 1 then configtable[column name][first row] 
      else if  [Deal Stage] = 2 then 0.5
      else if  [Deal Stage] = 3 then 0.9
      else if  [Deal Stage] = 4 then 0.9
      else if  [Deal Stage] = 5 then 1
      else 0,
      Replacer.ReplaceValue, {"Deal probability"}),
Solution

  • Make sure that configtable is its own powerquery table then

     let #"DealStageIntType" = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"DealProbability" = Table.ReplaceValue(#"DealStageIntType", each [Deal Probability], 
  each if  [Deal Stage] = 1 then configtable{0}[Column1]
  else if  [Deal Stage] = 2 then 0.5
  else if  [Deal Stage] = 3 then 0.9
  else if  [Deal Stage] = 4 then 0.9
  else if  [Deal Stage] = 5 then 1
  else 0,
  Replacer.ReplaceValue, {"Deal Probability"})
in  #"DealProbability"

    enter image description here

    Alternatively, create a range name for the one cell you want to use when DealStage=1, here assumed to be aaa and use the value in that range

     let #"DealStageIntType" = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"DealProbability" = Table.ReplaceValue(#"DealStageIntType", each [Deal Probability], 
  each if  [Deal Stage] = 1 then  Excel.CurrentWorkbook(){[Name="aaa"]}[Content]{0}[Column1]
  else if  [Deal Stage] = 2 then 0.5
  else if  [Deal Stage] = 3 then 0.9
  else if  [Deal Stage] = 4 then 0.9
  else if  [Deal Stage] = 5 then 1
  else 0,
  Replacer.ReplaceValue, {"Deal Probability"})
in  #"DealProbability"