Seeking Power Query function/query assistance.

Chaskel

New Member
Joined
Oct 15, 2017
Messages
2
Hello,

This is my initial foray into Power Query (which explains the broad title of this message) and after several days of searching and trying various things out I could use some assisted expertise.


  • I have a table (Classifiers) in its own worksheet (Sheet1) that has one column and currently has about 100 rows (which will grow as I add to it over time and my hope is to not set a max limit on the number of rows in whatever solution may be determined with your assistance).
  • I would like to be able to automatically read each of the rows in this worksheet and have each row value inserted where you see "classifier" in the function below.


  • I have another worksheet (Sheet2) in which I have a table (Filters) that contains a list of parameters (contains 100s of rows) to pass when making the web call in the function.
  • The list of parameters which help to craft the URL in that worksheet are then passed into the function as "DBFilter".
  • Both of these 2 items seem to be working well based on the query populating all of the rows in the worksheet that is spawned from the query (Sheet3).


  • In the query code below, you'll see I reference a 2nd variable and function which is essentially a duplicate for calling out the 2nd query separately. As mentioned above, I am wanting to have the classifier auto-populate in the function and in doing so, like my 2nd query below provides in the worksheet that is spawned (Sheet3), I'd like to have each of the custom columns take on the name of the classifier that is auto-populated in the function (which will be based on the names from each table row of Sheet1).

The current function:

let​
fnQueryDB = (DBFilter as text) =>​

let​
classifier = "'TestUsers'",​
url = "https://blah.com/search?jql=classifier%20=%20" & classifier & "%20" & DBFilter & "&Results=10",​
auth = [Headers=[Authorization="Basic <removed>"]],</removed>​
Source = Json.Document(Web.Contents(url,auth)),​

#"Converted to Table" = Record.ToTable(Source),​
Value1 = #"Converted to Table"{2}[Value]​
in​
Value1​

in​
fnQueryDB​

The current query which populates a worksheet (Sheet3) with the returned data from the web call made in the function:

let​
classifier1 = "TestUsers",​
classifier2 = "TestOwners",​
Source = Excel.CurrentWorkbook(){[Name="Filters"]}[Content],​
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DBFilter", type text}}),​

classifier1_Column = Table.AddColumn(#"Changed Type", ""&classifier1&"", each fnQueryDB_classifier1([DBFilter])),​
#"classifier2_Column" = Table.AddColumn(classifier1_Column, ""&classifier2&"", each fnQueryDB_classifier2([DBFilter]))​
in​
classifier2_Column​

Thank you for your time and assistance.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top