37

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?

Peter Albert
  • 16,917
  • 5
  • 64
  • 88

2 Answers2

74

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]
  1. 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.

Peter Albert
  • 16,917
  • 5
  • 64
  • 88
  • 14
    e.g. If cell is named as "Year", then the value can be fetched as -: Excel.CurrentWorkbook(){[Name="Year"]}[Content]{0}[Column1] – Ashish Chandra May 23 '17 at 08:47
  • 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 to type text" – SUMguy Mar 26 '18 at 20:02
  • 2
    @james5 You can just wrap the command any coersion function, e.g. `Text.From(GetValue("yourRange"))` or `Number.ToText(GetValue(...),...)` (see [Text.From](https://msdn.microsoft.com/en-us/library/mt253343.aspx) and [Number.ToText](https://msdn.microsoft.com/en-us/library/mt253385.aspx) – Peter Albert Mar 27 '18 at 19:33
  • How can I use this in a SQL query? – Soham Dasgupta Apr 04 '18 at 16:11
  • @SohamDasgupta Please ask a separate question for that. – TylerH Jan 22 '19 at 21:55
  • @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). – TylerH Jan 23 '19 at 21:18
  • 1
    @TylerH The `(rangeName) =>` notation makes it a function (with one parameter `rangeName`) which can be called from other queries – Peter Albert Jan 24 '19 at 12:14
  • 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. – Corepuncher Dec 31 '19 at 12:08
  • 1
    @Corepuncher 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. – Wayne Ivory Jan 17 '20 at 03:30
  • 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. – Chrstfer CB Feb 15 '23 at 04:48
  • 1
    @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. – Peter Albert Feb 16 '23 at 08:26
  • 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. – Chrstfer CB Feb 17 '23 at 02:41
  • “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… – Peter Albert Feb 18 '23 at 08:06
  • 1
    @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. – Tim Mar 14 '23 at 09:25
0

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"
Darren Harvey
  • 63
  • 5
  • 1
    what was the issue with the `=>` syntax? You need to do this in the advanved editor and replace everything with the `(rangeName) => ...` code! – Peter Albert Dec 01 '22 at 07:19