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
19,564 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,490 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
• 33,030 points
8,247 views
+1 vote
0 answers

How can I count the distinct values in a column using Power BI?

How can I count the distinct values ...READ MORE

Oct 23 in Power BI by Evanjalin
• 6,710 points
178 views
0 votes
0 answers

How can I reduce the size of a Power BI file (PBIX) when working with large datasets?

How can I reduce the size of ...READ MORE

Nov 5 in Power BI by Evanjalin
• 6,710 points
50 views
0 votes
1 answer

How can I troubleshoot the “A circular dependency was detected” error in complex DAX calculations?

The error message "A circular dependency was ...READ MORE

answered Nov 7 in Power BI by pooja
• 6,530 points
55 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
1,725 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,160 points
4,273 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
8,808 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
656 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,490 points
3,223 views
0 votes
2 answers

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

It's just a simple css code to ...READ MORE

answered Apr 19, 2020 in Power BI by Prateek Mehta
5,498 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP