PowerQuery : How can I reference a cell's value?

0 votes

I would like to feed the value of a cell in my Excel file but I've multiple PowerQuery queries. 

In this example, the full path to the source file name.

How can I get this into PowerQuery?

Feb 27 in Power BI by Upasana
• 8,550 points
926 views

1 answer to this question.

0 votes

You can do so using a named range and a custom function in PowerQuery:

  1. Name the cell you need to refer - e.g. SourceFile
  2. Insert a new blank PowerQuery query 
  3. In the PowerQuery editor, go to View > Advanced Editor and paste the following code;
(rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]
  1. Name the query to GetValue

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

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

answered Feb 27 by Shubham
• 13,310 points

Related Questions In Power BI

0 votes
1 answer

How can I get a column value from previous row in Power Query?

Hi Sindhu, add this line as your ...READ MORE

answered Mar 18 in Power BI by Cherukuri
• 31,840 points
1,041 views
0 votes
1 answer

Can I create a Power BI report in Office 365 without my locally installed Office Excel?

Yess!! You can achieve this by using ...READ MORE

answered Sep 26, 2018 in Power BI by Kalgi
• 42,430 points
89 views
0 votes
1 answer

How do I know who in my organization has a Power BI account?

You can view the Azure Active Directory ...READ MORE

answered Oct 15, 2018 in Power BI by Hannah
• 16,570 points
54 views
0 votes
1 answer

how can I connect my web api with Power BI Reports?

Go to Home > Edit Queries > ...READ MORE

answered Oct 18, 2018 in Power BI by Annie97
• 2,190 points
1,689 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14 in Power BI by Upasana
• 8,550 points
35 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
308 views
0 votes
1 answer

Power Query : http POST request with form data

Try using Uri.BuildQueryString and Json.Document. let ...READ MORE

answered Dec 12, 2018 in Power BI by Upasana
• 8,550 points
1,513 views
0 votes
1 answer

Power Query: Adding a Special Case

Try adding a Group By with Minimum ...READ MORE

answered Dec 27, 2018 in Power BI by Upasana
• 8,550 points
46 views
0 votes
1 answer

How can I concatenate grouped values in PowerQuery ?

If your table is Source, and if ...READ MORE

answered Feb 27 in Power BI by Shubham
• 13,310 points
324 views
0 votes
1 answer

Can I get rid of the grey border on the report visuals with power bi embedded?

You can do the following to make the ...READ MORE

answered Apr 10 in Power BI by Shubham
• 13,310 points
363 views