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, 2019 in Power BI by Upasana
• 8,620 points
8,738 views
Thank you for the solution. Appreciate it!

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, 2019 by Shubham
• 13,450 points
Yeah!!!

It works.

Thanks

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, 2019 in Power BI by Cherukuri
• 32,490 points
4,827 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
• 51,970 points
220 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
• 18,410 points
129 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,200 points
5,480 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, 2019 in Power BI by Upasana
• 8,620 points
421 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,200 points
1,657 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,620 points
4,845 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,620 points
101 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, 2019 in Power BI by Shubham
• 13,450 points
1,331 views
0 votes
2 answers