Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
@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
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"
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
Great stuff! Love it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.