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.
Hi,
I'm trying to extract the data from a JSON URL which has only one field like the following:
The integer, in this case 1178, needs to be read from the Advanced Editor in Power BI. It's a variable that keeps changing as more tickets are created. This number needs to be stored and will get used later on in the query.
Is there a simple way to achieve this? So far, i've got to:
GetEntityCount = () => let Url = "http://url.freshdesk.com/helpdesk/tickets/summary.json?view_name=all", Json = GetJson(Url), EnityCount = ([Json] ("view_count")) in EntityCount
This obviously doesn't work, I've tried multiple things but I can't get it to work.
So to be short:
Extract 1 field from a JSON URL / REST API.
Store this field into a variable.
Done.
Solved! Go to Solution.
Our in-house developer managed to get it fixed, posting this for other people who are struggling:
Our in-house developer managed to get it fixed, posting this for other people who are struggling:
If more information need to be provided, please let me know.
Here's the full query:
let BaseUrl = "https://servicedeskurl.freshdesk.com/api/v2/tickets?updated_since=2010-01-01&per_page=100&page=", EntitiesPerPage = 100, GetJson = (Url) => let Options = [Headers=[Authorization="myApiKey"]], RawData = Web.Contents(Url, Options), Json = Json.Document(RawData) in Json, GetPage = (Index) => let Page = Text.From(Index), Url = BaseUrl & Page, Json = GetJson(Url), Value = Json in Value, EntityCount = 1174, PageCount = Number.RoundUp(EntityCount / EntitiesPerPage), PageIndices = { 1 .. PageCount }, Pages = List.Transform(PageIndices, each GetPage(_)), Entities = List.Union(Pages), Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"cc_emails", "fwd_emails", "reply_cc_emails", "ticket_cc_emails", "fr_escalated", "spam", "email_config_id", "group_id", "priority", "requester_id", "responder_id", "source", "company_id", "status", "subject", "association_type", "to_emails", "product_id", "id", "type", "due_by", "fr_due_by", "is_escalated", "custom_fields", "created_at", "updated_at", "associated_tickets_count", "tags"}, {"Column1.cc_emails", "Column1.fwd_emails", "Column1.reply_cc_emails", "Column1.ticket_cc_emails", "Column1.fr_escalated", "Column1.spam", "Column1.email_config_id", "Column1.group_id", "Column1.priority", "Column1.requester_id", "Column1.responder_id", "Column1.source", "Column1.company_id", "Column1.status", "Column1.subject", "Column1.association_type", "Column1.to_emails", "Column1.product_id", "Column1.id", "Column1.type", "Column1.due_by", "Column1.fr_due_by", "Column1.is_escalated", "Column1.custom_fields", "Column1.created_at", "Column1.updated_at", "Column1.associated_tickets_count", "Column1.tags"}), #"Expanded Column1.custom_fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.custom_fields", {"product", "cf_customerticketsystemreference", "vendor", "product_version", "external_ticket_vendor", "defect_bug_tracking"}, {"Column1.custom_fields.product", "Column1.custom_fields.cf_customerticketsystemreference", "Column1.custom_fields.vendor", "Column1.custom_fields.product_version", "Column1.custom_fields.external_ticket_vendor", "Column1.custom_fields.defect_bug_tracking"}), #"Extracted Values" = Table.TransformColumns(#"Expanded Column1.custom_fields", {"Column1.tags", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Changed Type" = Table.TransformColumnTypes(#"Extracted Values",{{"Column1.status", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","2","Open",Replacer.ReplaceText,{"Column1.status"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","3","Pending",Replacer.ReplaceText,{"Column1.status"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","4","Resolved",Replacer.ReplaceText,{"Column1.status"}), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","5","Closed",Replacer.ReplaceText,{"Column1.status"}), #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","6","Defect",Replacer.ReplaceText,{"Column1.status"}), #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","7","Enhancement Request",Replacer.ReplaceText,{"Column1.status"}) in #"Replaced Value5"
This actually works but as you can see, I'm entering the EntityCount manually.
I want to get the EntityCount via the URL, this URL shows {"view_count":####} and i want to retrieve that #### and store it into a variable.
I hope my issue is clear enough to understand.
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.
User | Count |
---|---|
13 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
22 | |
3 | |
2 | |
2 | |
2 |