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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,916
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,113,907
Messages
5,544,984
Members
410,647
Latest member
LegenDSlayeR
Top