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.
The current function:
The current query which populates a worksheet (Sheet3) with the returned data from the web call made in the function:
Thank you for your time and assistance.
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.