JSON query based on another query

[adam_cogswell]

Hi,

New to PowerBI, and having what is probably a simple problem but can't seem to get it figured. I am trying to load JSON data and I've got one query working like a charm, transformed into a table and performing as expected. My problem is the second query is based on information from the first (an ID field) and the third is based on information from the second (another ID field). The way the data API works that I'm accessing, I can't get all the data together so it will have to be three separate queries / tables.

My question is how do I build the second query to reference data from the first? I tried using the table name column structure (Table[Column]) as part of my URL string, but I get an error that says "Expression.Error: We cannot apply operator & to types Text and List.". I am positive I'm missing something little but I can't figure it out for the life of me. Here's the query for the first table, followed by the one I'm trying to use for the second:

First Table / Query = Surveys

let
    Source = Json.Document(Web.Contents("https://api.provider.com/" & "surveys?site_id=15626", [Headers=[Authorization="Bearer ACCESS_TOKEN", Accept="application/json", #"NGIN-API-VERSION"="0.1"]])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "site_id", "created_at", "updated_at", "status", "tst_rate_group_num", "code", "survey_type", "registrar_emails", "allow_multiple_registrations", "next_event", "rep_user_id", "monetary", "open_date", "close_date", "bank_account_id", "merchant_account_id", "custom_message", "login_optional", "user_id", "has_barcode", "barcode_type", "group_range", "group_range_question_element_id", "editable_by_user", "form_label", "sub_form_label", "custom_css", "user_can_add_entries", "skip_welcome_page", "event_id", "customer_pays_fee", "offer_id", "allow_amex", "next_form_text_override", "restrict_invite", "background_screen_enabled", "approval_user_id"}, {"Column1.id", "Column1.name", "Column1.site_id", "Column1.created_at", "Column1.updated_at", "Column1.status", "Column1.tst_rate_group_num", "Column1.code", "Column1.survey_type", "Column1.registrar_emails", "Column1.allow_multiple_registrations", "Column1.next_event", "Column1.rep_user_id", "Column1.monetary", "Column1.open_date", "Column1.close_date", "Column1.bank_account_id", "Column1.merchant_account_id", "Column1.custom_message", "Column1.login_optional", "Column1.user_id", "Column1.has_barcode", "Column1.barcode_type", "Column1.group_range", "Column1.group_range_question_element_id", "Column1.editable_by_user", "Column1.form_label", "Column1.sub_form_label", "Column1.custom_css", "Column1.user_can_add_entries", "Column1.skip_welcome_page", "Column1.event_id", "Column1.customer_pays_fee", "Column1.offer_id", "Column1.allow_amex", "Column1.next_form_text_override", "Column1.restrict_invite", "Column1.background_screen_enabled", "Column1.approval_user_id"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1.id", "ID"}, {"Column1.name", "Name"}, {"Column1.site_id", "SiteID"}, {"Column1.created_at", "CreatedDate"}, {"Column1.updated_at", "UpdatedDate"}, {"Column1.status", "Status"}, {"Column1.tst_rate_group_num", "RateGroupNum"}, {"Column1.code", "Code"}, {"Column1.survey_type", "SurveyType"}, {"Column1.registrar_emails", "RegistrarEmails"}, {"Column1.allow_multiple_registrations", "AllowMultiples"}, {"Column1.next_event", "NextEvent"}, {"Column1.rep_user_id", "UserID"}, {"Column1.monetary", "Monetary"}, {"Column1.open_date", "OpenDate"}, {"Column1.close_date", "CloseDate"}, {"Column1.bank_account_id", "BankAcctID"}, {"Column1.merchant_account_id", "MerchantAcctID"}, {"Column1.custom_message", "CustomMessage"}, {"Column1.login_optional", "LoginOptional"}, {"Column1.user_id", "User_ID"}, {"Column1.has_barcode", "HasBarcode"}, {"Column1.barcode_type", "BarcodeType"}, {"Column1.group_range", "GroupRange"}, {"Column1.editable_by_user", "UserCanEdit"}, {"Column1.form_label", "FormLabel"}, {"Column1.sub_form_label", "SubFormLabel"}, {"Column1.custom_css", "CustomCSS"}, {"Column1.user_can_add_entries", "UserCanAddEntries"}, {"Column1.skip_welcome_page", "SkipWelcomePage"}, {"Column1.event_id", "EventID"}, {"Column1.customer_pays_fee", "CustomerPaysFee"}, {"Column1.offer_id", "OfferID"}, {"Column1.allow_amex", "AllowAmex"}, {"Column1.next_form_text_override", "NextFormTextOverride"}, {"Column1.restrict_invite", "RestrictInvite"}, {"Column1.background_screen_enabled", "BackgroundScreenEnabled"}, {"Column1.approval_user_id", "ApprovalUserID"}})
in
    #"Renamed Columns"

Second Table/Query = SurveyResults

let
    Source = Json.Document(Web.Contents("https://api.provider.com/survey_results?survey_id=" & Surveys[ID],
        [Headers=[Authorization="Bearer ACCESS_TOKEN", Allow="application/json", #"NGIN-API-VERSION"="0.1"]]))
in
    Source

I'd greatly appreciate any information or help. I know the error message is referring to some sort of type mismatch between the URL and what I'm trying to append to it, but I figured I was just making that reference incorrectly. If I can get this sorted for the second table, I'm sure I can apply the same logic to the third table / query. Thanks in advance!

[dkay84_PowerBI]

For your second table (SurveyResults), in your web call I'm assuming that the [ID] field is a column of id's in the first table?

You can convert your second query to a funciton which you then invoke (via Add Column) and it will read each [ID] value from the [ID] field and run the web call.

If this scenario sounds correct, I can give you more details on how to implement, although it is very simple and can be done almost entirely via the UI.

[adam_cogswell]

Hi, 

You're correct. The ID field is a column of IDs in the first table. I'd love to hear how to accomplish this. Thanks. 

[dkay84_PowerBI]

A good general overview is here:

http://radacad.com/custom-functions-made-easy-in-power-bi-desktop

I usually follow the pattern of just changing my M code to indicate it is function and passing in a value.  For example, change your M code for the second query to the following:

let function = (ID as text) =>
    Source = Json.Document(Web.Contents("https://api.provider.com/survey_results?survey_id=" & ID,
        [Headers=[Authorization="Bearer ACCESS_TOKEN", Allow="application/json", #"NGIN-API-VERSION"="0.1"]]))
in
    Source
in
    function

Then, in your original query/table with the list of IDs, go to Add Column > Invoke Custom Function, choose the function you just made as the function query, and change the table drop down from text to column name and select the [ID] column.

This should then run your API call for every line in your table, plugging in the ID field for each row into the function and return the result.  Note, you will have to expand the result column by clicking on the expand icon in header of the column.

[adam_cogswell]

Hi,

Thanks for the reference information, and the M code. I was trying to use your code just to get more familiar with the language, but it's throwing a "Token EoF expected." error when I try to save it. Seems to dislike the two "in" statements at the end:

let 
    function = (ID as text) =>
    Source = Json.Document(Web.Contents("https://api.provider.com/survey_results?survey_id=" & ID,
        [Headers=[Authorization="Bearer ACCESS_TOKEN", Allow="application/json", #"NGIN-API-VERSION"="0.1"]]))
in
    function
in
    Source

[dkay84_PowerBI]

You switched the two "in" statements compared to my example

[adam_cogswell]

Sorry, I had re-arranged them trying to troubleshoot it. It doesn't work either way...

[dkay84_PowerBI]

To be clear, you are getting the error just trying to save the query/function, and not even at the point of invoking it, correct?

[adam_cogswell]

Correct. I click "Done" and it shows that error. It also appears in the Advanced Editor before I even click the button. 

[dkay84_PowerBI · Accepted solution]

Sorry I ommitted a "let".  See below:

let function = (ID as text) => let
    Source = Json.Document(Web.Contents("https://api.provider.com/survey_results?survey_id=" & ID,
        [Headers=[Authorization="Bearer ACCESS_TOKEN", Allow="application/json", #"NGIN-API-VERSION"="0.1"]]))
in
    Source
in
    function

[adam_cogswell]

No apologies necessary. That seems to have done the trick, the function is validating now. I just need to test the rest of that workflow. Thanks for all your help!

[dkay84_PowerBI]

Since you said you are new to PBI, do you know how to create a function in the query editor?

[adam_cogswell]

Not specifically for PBI, no. I have had plenty of experience in other platforms and tools with functions, however.