Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
svishwanathan
Helper III
Helper III

RelativePath & Query Web.Content

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
 
 
 
 
6 REPLIES 6
pgpacheco
New Member

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
Employee
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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"

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.

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/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

Great stuff! Love it.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors