How to dynamically pass cell values as a parameter for new PowerQuery?

Alwexis

New Member
Joined
Jun 16, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I created a connection to REST API using PowerQuery in Excel2016 and it gets me information about companies.

In a certain query table, after the results are loaded, there is a column with ID of the company. Now, i want to be able to click on some id and this could be passed to my new query with this id as a parameter in a header. My connection string looks like this:

let
Source = Json.Document(Web.Contents("https://rejestr.io/api/v1/persons/"& Excel.CurrentWorkbook(){[Name="ID"]}[Content]{0}[Column1] &"/relations", [Headers=[Authorization="xxxxxxxxx"]]))
<..rest of the code, mainly formatting...>
in
"ColumnChanged"



Here im referencing the ID from a certain cell (user provided), but i want to be able to pass in this place a value from just selected cell on ID column and then a new query should be created and loaded onto a new worksheet.

I was thinking about this function to "get" a value cell from that column:

Worksheet_SelectionChange(ByVal Target As Range)

But i cannot figure out how to launch a new power query with that...

Alex
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,949
Office Version
  1. 2016
Platform
  1. Windows
There are a number of ways of doing this on various websites - its probably easiest to search for 'how to pass file path into powerquery' and take your pick (thats what I've just done to check where I found out how to do it).

I've done it and you can adapt the technique however you wish. Its quite flexible - but I don't know it well enough to explain.

Not a great answer, but hopefully provides a pointer. (and at least you now Know its possible).
 

Watch MrExcel Video

Forum statistics

Threads
1,118,309
Messages
5,571,468
Members
412,395
Latest member
nielsvanlit
Top