How can I reference a cell's value in PowerQuery

Question

I'm having multiple PowerQuery queries that I would like to feed the value of a cell in my Excel file. In this particular case, the full path to the sourcefile name.

Is there any way I can get this into PowerQuery?

Ipa

Answer 1

This can be achieved using a named range and a custom function in PowerQuery:

1. Name the cell you need to refer (type in a name into the file left of the formula bar) - e.g. SourceFile
2. Insert a new blank PowerQuery query (PowerQuery ribbon -> From other sources)
3. In the PowerQuery editor, go to View -> Advanced Editor. Remove the existing code and instead paste the following code;

(rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

4. Name the query to GetValue (Name property in the Query settings pane on the right)

Now you can access the named cell in your queries, using GetValue(cellName) - e.g.

= Excel.Workbook(File.Contents(GetValue("SourceFile")))

If the cell is part of an Excel table, the above is not needed - you can import/access that table's data directly using the "From Table/Range" button in the "Data" ribbon.

Ipa

e.g. If cell is named as "Year", then the value can be fetched as -: Excel.CurrentWorkbook(){[Name="Year"]}[Content]{0}[Column1] – Unheard

How can I change the data type for this? I am trying to compare the value of the parameter to text, but it says "cannot convert value <parameter value> to type text" – Deformation

@james5 You can just wrap the command any coersion function, e.g. Text.From(GetValue("yourRange")) or Number.ToText(GetValue(...),...) (see Text.From and Number.ToText – Ipa

How can I use this in a SQL query? – Alden

@SohamDasgupta Please ask a separate question for that. – Borg

@PeterAlbert I don't quite understand the (rangeName) => assignment part, but couldn't get that to work. What did work was just inserting that named range lookup function inline in my comparison function (e.g. the bit from Ashish's comment). – Borg

@Borg The (rangeName) => notation makes it a function (with one parameter rangeName) which can be called from other queries – Ipa

I cannot get past step 1. I assume he is talking about the power query editor? I found out how to display the formula bar, but cannot figure out what is "left of the formula bar" (where to type a name?). Maybe his answer does not apply to newer versions of Excel? I have a simple table from the web, and a user-defined value in a cell. I want to add that value into the query URL (append to string) so table data is dynamic. – Fervent

@Fervent This is where you normally see the current cell address displayed, e.g. D12. You can select any cell (or range of cells) and just type a name in there to assign it to a named range. – Lilli

Excel tables act as named ranges for this purpose (and others I think), so if you have a table you can skip step 1 and put the table's name in for the range name. I can't suggest an edit right now, so this was the best way to convey that information. – Measured

@ChristopherCarriganBrolly Yes, tables can be referenced directly - but challenge was how to access any cell outside of a table. I'll clarify it in the answer. – Ipa

Mostly agree, thought slight semantic nitpick. The question doesn't specify the cell is outside of a table, just that it is a cell. If it is currently outside a table and you can convert it or the region it's in into one, that's (imo) the most user friendly way to take advantage here. – Measured

“Wrapping” a single cell into a table feels like too much overhead to me - but I guess YMMV. If it already is in a table then it is a different story of course. Just that this was not the original issue at hand - as I found it so easy to get table data into PQ but frustratingly non-obvious for single cells. Esp. when coming from VBA where accessing any cell/range/table can be done a myriad, simple ways - that often don’t differentiate if the name is a table/named range or cell address… – Ipa

@PeterAlbert You mentioned in a comment to the other answer that one has to remove all code in the Advanced Query Editor before pasting the one from above in step 3. This was exactly why it didn't work for me. You should add that to your answer. – Demetricedemetris

Answer 2

I couldn't get the => syntax to work so what I ended up with is as below. Here I'm using a single table to store all the values but you could also use named ranges and stick to using [content]{0}[Column1] for each one.

    let
        SITE_URL_VALUE = Excel.CurrentWorkbook(){[Name="SETTINGS_TABLE"]}[Content]{0}[Value],
        FOLDER_PATH_VALUE = Excel.CurrentWorkbook(){[Name="SETTINGS_TABLE"]}[Content]{1}[Value],
        FILENAME_VALUE = Excel.CurrentWorkbook(){[Name="SETTINGS_TABLE"]}[Content]{2}[Value],

    Source = SharePoint.Files(SITE_URL_VALUE, [ApiVersion = 15]),    
    #"Import Filename" = Source{[Name=FILENAME_VALUE, #"Folder Path"=FOLDER_PATH_VALUE]}[Content],
    #"Import Workbook" = Excel.Workbook(#"Import Filename"),
    #"Import Table" = #"Import Workbook"{[Item="ACTIVITY_PLAN_TABLE",Kind="Table"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(#"Import Table",{"Work Order", "MAT", "Exp Hours", "OSC", "Team", "Plant", "Area", "Hours", "Complete", "Plan Status"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Work Order", type text}, {"MAT", type text}})
in
    #"Changed Type"

Northwest

what was the issue with the => syntax? You need to do this in the advanved editor and replace everything with the (rangeName) => ... code! – Ipa