RelativePath & Query Web.Content

[svishwanathan]

Hello

I am trying to tweak a Web.Contents code so i can try to publish on the gateway. This code works fine on the desktop

let

   Source = Json.Document(Web.Contents("https://support.xxxxxx.com/helpdesk/tickets/view/1000189524?format=json&page="& Number.ToText(page),[Headers=[Authorization="Basic xxxxxxxxx", #"Content_Type"="application/json"]])),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"cc_email", "created_at", "deleted", "department_id_value", "display_id", "due_by", "email_config_id", "frDueBy", "fr_escalated", "group_id", "id", "isescalated", "owner_id", "priority", "requester_id", "responder_id", "source", "spam", "status", "subject", "ticket_type", "to_email", "updated_at", "description", "description_html", "status_name", "requester_status_name", "priority_name", "source_name", "requester_name", "responder_name", "to_emails", "department_name", "assoc_problem_id", "assoc_change_id", "assoc_change_cause_id", "assoc_asset_id", "custom_field"}, {"Column1.cc_email", "Column1.created_at", "Column1.deleted", "Column1.department_id_value", "Column1.display_id", "Column1.due_by", "Column1.email_config_id", "Column1.frDueBy", "Column1.fr_escalated", "Column1.group_id", "Column1.id", "Column1.isescalated", "Column1.owner_id", "Column1.priority", "Column1.requester_id", "Column1.responder_id", "Column1.source", "Column1.spam", "Column1.status", "Column1.subject", "Column1.ticket_type", "Column1.to_email", "Column1.updated_at", "Column1.description", "Column1.description_html", "Column1.status_name", "Column1.requester_status_name", "Column1.priority_name", "Column1.source_name", "Column1.requester_name", "Column1.responder_name", "Column1.to_emails", "Column1.department_name", "Column1.assoc_problem_id", "Column1.assoc_change_id", "Column1.assoc_change_cause_id", "Column1.assoc_asset_id", "Column1.custom_field"}),

    #"Expanded Column1.cc_email" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.cc_email", {"cc_emails", "fwd_emails", "reply_cc", "tkt_cc"}, {"Column1.cc_email.cc_emails", "Column1.cc_email.fwd_emails", "Column1.cc_email.reply_cc", "Column1.cc_email.tkt_cc"}),

    #"Expanded Column1.cc_email.tkt_cc" = Table.ExpandListColumn(#"Expanded Column1.cc_email", "Column1.cc_email.tkt_cc"),

    #"Expanded Column1.cc_email.reply_cc" = Table.ExpandListColumn(#"Expanded Column1.cc_email.tkt_cc", "Column1.cc_email.reply_cc"),

    #"Expanded Column1.cc_email.fwd_emails" = Table.ExpandListColumn(#"Expanded Column1.cc_email.reply_cc", "Column1.cc_email.fwd_emails"),

    #"Expanded Column1.cc_email.cc_emails" = Table.ExpandListColumn(#"Expanded Column1.cc_email.fwd_emails", "Column1.cc_email.cc_emails"),

    #"Expanded Column1.custom_field" = Table.ExpandRecordColumn(#"Expanded Column1.cc_email.cc_emails", "Column1.custom_field", {"level_2_12133", "level_3_12133", "preferred_method_of_contact_12133", "best_time_to_contact_12133", "phone_number_12133", "level_1_12133", "office_location_12133", "job_number_12133"}, {"Column1.custom_field.level_2_12133", "Column1.custom_field.level_3_12133", "Column1.custom_field.preferred_method_of_contact_12133", "Column1.custom_field.best_time_to_contact_12133", "Column1.custom_field.phone_number_12133", "Column1.custom_field.level_1_12133", "Column1.custom_field.office_location_12133", "Column1.custom_field.job_number_12133"}),

    #"Sorted Rows" = Table.Sort(#"Expanded Column1.custom_field",{{"Column1.display_id", Order.Ascending}})

in

This fails on the service. From what i have read, i need to tweak this to include relative path. I tried but have failed

let

   Source = Json.Document(Web.Contents("https://support.swinerton.com/helpdesk/tickets",[Headers=[Authorization="Basic TmVnVzBSUVlpeUFYb29lcnVkWg==", #"Content_Type"="application/json"]],

     [RelativePath="/view/1000189524?format=json&page=" & Number.ToText(page),

    Query=[Authorization="Basic TmVnVzBSUVlpeUFYb29lcnVkWg==", #"Content_Type"="application/json"]])),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"cc_email", "created_at", "deleted", "department_id_value", "display_id", "due_by", "email_config_id", "frDueBy", "fr_escalated", "group_id", "id", "isescalated", "owner_id", "priority", "requester_id", "responder_id", "source", "spam", "status", "subject", "ticket_type", "to_email", "updated_at", "description", "description_html", "status_name", "requester_status_name", "priority_name", "source_name", "requester_name", "responder_name", "to_emails", "department_name", "assoc_problem_id", "assoc_change_id", "assoc_change_cause_id", "assoc_asset_id", "custom_field"}, {"Column1.cc_email", "Column1.created_at", "Column1.deleted", "Column1.department_id_value", "Column1.display_id", "Column1.due_by", "Column1.email_config_id", "Column1.frDueBy", "Column1.fr_escalated", "Column1.group_id", "Column1.id", "Column1.isescalated", "Column1.owner_id", "Column1.priority", "Column1.requester_id", "Column1.responder_id", "Column1.source", "Column1.spam", "Column1.status", "Column1.subject", "Column1.ticket_type", "Column1.to_email", "Column1.updated_at", "Column1.description", "Column1.description_html", "Column1.status_name", "Column1.requester_status_name", "Column1.priority_name", "Column1.source_name", "Column1.requester_name", "Column1.responder_name", "Column1.to_emails", "Column1.department_name", "Column1.assoc_problem_id", "Column1.assoc_change_id", "Column1.assoc_change_cause_id", "Column1.assoc_asset_id", "Column1.custom_field"}),

    #"Expanded Column1.cc_email" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.cc_email", {"cc_emails", "fwd_emails", "reply_cc", "tkt_cc"}, {"Column1.cc_email.cc_emails", "Column1.cc_email.fwd_emails", "Column1.cc_email.reply_cc", "Column1.cc_email.tkt_cc"}),

    #"Expanded Column1.cc_email.tkt_cc" = Table.ExpandListColumn(#"Expanded Column1.cc_email", "Column1.cc_email.tkt_cc"),

    #"Expanded Column1.cc_email.reply_cc" = Table.ExpandListColumn(#"Expanded Column1.cc_email.tkt_cc", "Column1.cc_email.reply_cc"),

    #"Expanded Column1.cc_email.fwd_emails" = Table.ExpandListColumn(#"Expanded Column1.cc_email.reply_cc", "Column1.cc_email.fwd_emails"),

    #"Expanded Column1.cc_email.cc_emails" = Table.ExpandListColumn(#"Expanded Column1.cc_email.fwd_emails", "Column1.cc_email.cc_emails"),

    #"Expanded Column1.custom_field" = Table.ExpandRecordColumn(#"Expanded Column1.cc_email.cc_emails", "Column1.custom_field", {"level_2_12133", "level_3_12133", "preferred_method_of_contact_12133", "best_time_to_contact_12133", "phone_number_12133", "level_1_12133", "office_location_12133", "job_number_12133"}, {"Column1.custom_field.level_2_12133", "Column1.custom_field.level_3_12133", "Column1.custom_field.preferred_method_of_contact_12133", "Column1.custom_field.best_time_to_contact_12133", "Column1.custom_field.phone_number_12133", "Column1.custom_field.level_1_12133", "Column1.custom_field.office_location_12133", "Column1.custom_field.job_number_12133"}),

    #"Sorted Rows" = Table.Sort(#"Expanded Column1.custom_field",{{"Column1.display_id", Order.Ascending}})

in

    #"Sorted Rows"

I get error

[pgpacheco]

Use [Query = [page = Number.ToText(page)]], always use query when using variables into PBI Service 😁

I use like this:

(offset) =>
let
    Source =
        Json.Document(
            Web.Contents(
                "https://api.myDatabase.com.br/myEnterprise/public/api/v1/endpoint-api?limit=200"
                , [Query =[
                    offset = Number.ToText(offset)
                    , initDate = Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), -0), [Format="yyyy-MM-dd"])
                    ]
                ]
            )
        )
in
    Source

 And after I create another Null File and use List.Generate() to pass the variable calling the function above.

[v-yuezhe-msft]

@svishwanathan,

Please check your code to the following and check if it works.

let
   Source = Json.Document(Web.Contents("https://support.xxxxxx.com/",[RelativePath="helpdesk/tickets/view/1000189524?format=json&page="& Number.ToText(page),Headers=[Authorization="Basic xxxxxxxxx", #"Content_Type"="application/json"]])),    
   #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"cc_email", "created_at", "deleted", "department_id_value", "display_id", "due_by", "email_config_id", "frDueBy", "fr_escalated", "group_id", "id", "isescalated", "owner_id", "priority", "requester_id", "responder_id", "source", "spam", "status", "subject", "ticket_type", "to_email", "updated_at", "description", "description_html", "status_name", "requester_status_name", "priority_name", "source_name", "requester_name", "responder_name", "to_emails", "department_name", "assoc_problem_id", "assoc_change_id", "assoc_change_cause_id", "assoc_asset_id", "custom_field"}, {"Column1.cc_email", "Column1.created_at", "Column1.deleted", "Column1.department_id_value", "Column1.display_id", "Column1.due_by", "Column1.email_config_id", "Column1.frDueBy", "Column1.fr_escalated", "Column1.group_id", "Column1.id", "Column1.isescalated", "Column1.owner_id", "Column1.priority", "Column1.requester_id", "Column1.responder_id", "Column1.source", "Column1.spam", "Column1.status", "Column1.subject", "Column1.ticket_type", "Column1.to_email", "Column1.updated_at", "Column1.description", "Column1.description_html", "Column1.status_name", "Column1.requester_status_name", "Column1.priority_name", "Column1.source_name", "Column1.requester_name", "Column1.responder_name", "Column1.to_emails", "Column1.department_name", "Column1.assoc_problem_id", "Column1.assoc_change_id", "Column1.assoc_change_cause_id", "Column1.assoc_asset_id", "Column1.custom_field"}),
    #"Expanded Column1.cc_email" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.cc_email", {"cc_emails", "fwd_emails", "reply_cc", "tkt_cc"}, {"Column1.cc_email.cc_emails", "Column1.cc_email.fwd_emails", "Column1.cc_email.reply_cc", "Column1.cc_email.tkt_cc"}),
    #"Expanded Column1.cc_email.tkt_cc" = Table.ExpandListColumn(#"Expanded Column1.cc_email", "Column1.cc_email.tkt_cc"),
    #"Expanded Column1.cc_email.reply_cc" = Table.ExpandListColumn(#"Expanded Column1.cc_email.tkt_cc", "Column1.cc_email.reply_cc"),
    #"Expanded Column1.cc_email.fwd_emails" = Table.ExpandListColumn(#"Expanded Column1.cc_email.reply_cc", "Column1.cc_email.fwd_emails"),
    #"Expanded Column1.cc_email.cc_emails" = Table.ExpandListColumn(#"Expanded Column1.cc_email.fwd_emails", "Column1.cc_email.cc_emails"),
    #"Expanded Column1.custom_field" = Table.ExpandRecordColumn(#"Expanded Column1.cc_email.cc_emails", "Column1.custom_field", {"level_2_12133", "level_3_12133", "preferred_method_of_contact_12133", "best_time_to_contact_12133", "phone_number_12133", "level_1_12133", "office_location_12133", "job_number_12133"}, {"Column1.custom_field.level_2_12133", "Column1.custom_field.level_3_12133", "Column1.custom_field.preferred_method_of_contact_12133", "Column1.custom_field.best_time_to_contact_12133", "Column1.custom_field.phone_number_12133", "Column1.custom_field.level_1_12133", "Column1.custom_field.office_location_12133", "Column1.custom_field.job_number_12133"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Column1.custom_field",{{"Column1.display_id", Order.Ascending}})
in 
    #"Sorted Rows"

Regards,
Lydia

[suelenecs]

Helo, @Anonymous .

How to set relative path in this query?

let
url = "https://api.movidesk.com/public/v1/tickets?token=d8d68ba4-b7f3-4d96-9f00-3eb69aa573e8&$select=id,type,subject,origin,urgency,category,status,baseStatus,serviceFirstLevelId,serviceFirstLevel,serviceSecondLevel,serviceThirdLevel,resolvedIn,closedIn,reopenedIn,lifeTimeWorkingTime,stoppedTime,stoppedTimeWorkingTime,resolvedInFirstCall,createdDate&$expand=owner,createdBy,clients,clients($expand=organization),customFieldValues($expand=items)&$filter=createdDate gt 2016-10-01&$top=100&$skip=",
skip = 0,
// Retorna lista de valores
return =
List.Generate(
// Valor inicial
()=>
[result= try Json.Document(Web.Contents(url & Number.ToText(skip))) otherwise null, skip = 0],
each List.Count([result]) <> skip,
each [result = try Json.Document(Web.Contents(url & Number.ToText([skip]+100))) otherwise null, skip =[skip]+100],
each [result]
),
#"Convertido para Tabela" = Table.FromList(return, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in #"Convertido para Tabela"

[jjrscorpio]

Hi Suelenecs,

You should try it like shown below:

url = "https://api.movidesk.com"
Web.Contents(url,
[
RelativePath ="public/v1/tickets?token=d8d68ba4-b7f3-4d96-9f00-3eb69aa573e8&$select=id,type,subject,origin,urgency,category,status,baseStatus,serviceFirstLevelId,serviceFirstLevel,serviceSecondLevel,serviceThirdLevel,resolvedIn,closedIn,reopenedIn,lifeTimeWorkingTime,stoppedTime,stoppedTimeWorkingTime,resolvedInFirstCall,createdDate&$expand=owner,createdBy,clients,clients($expand=organization),customFieldValues($expand=items)&$filter=createdDate" & Number.ToText(skip)
]
) otherwise null, skip = 0],
each List.Count([result]) <> skip,

I think you can figure out the other one.

[ImkeF]

This article sums up the relevant aspects around this topic: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

[Anonymous]

Great stuff! Love it.