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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Watch MrExcel Video

Forum statistics

Threads
1,118,415
Messages
5,571,977
Members
412,430
Latest member
Huuktkt
Top