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,430 points
42 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,190 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
• 17,870 points
125 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
• 37,320 points
47 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
• 14,080 points
31 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
779 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,430 points
17 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
114 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,430 points
715 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,430 points
23 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,190 points
81 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,190 points
166 views