PDA

View Full Version : reference a cell's value in Power Query



irishbread
2015-08-14, 08:27 PM
I have named a cell in Excel 2010 "CurSelect" (derived from the selection made in a dropdown form control) - currently, the only two options from the dropdown are "Par Value" and "Market Value"

When refreshing the table, the "Value" column (see formula below) always returns [MktVal] field data, regardless of whether "Par Value" or "Market Value" is selected from the dropdown. What am I missing?

Thanks.


= Table.AddColumn(#"Changed Type", "Value", each if fnGetValue = "Par Value" then [ParVal] else [MktVal])



let fnGetValue=(CurSelect) =>
let
name = Excel.CurrentWorkbook(){[Name=CurSelect]}[Content],
value = name{0}[Column1]
in
value
in
fnGetValue

Herbds7
2015-08-14, 11:23 PM
Excel 2010 with free Power Query Add-In,
or Excel 2013 Pro Plus.
...Power Query can use data from the current workbook if the data is formatted as a Table.
It cannot read data from individual cells or named ranges...
Quoted from the book "Power Query for Power BI and Excel"
by Chris Webb.