User Profile

Stephanvk

Frequent Visitor

Joined 7 years ago

3 Posts1 Solution

View All Badges

User Widgets

Contributions

Re: Extract data from JSON URL
Our in-house developer managed to get it fixed, posting this for other people who are struggling:
7 years ago Place Developer
3.3KViews
0likes
0Comments
Re: Extract data from JSON URL
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.
7 years ago Place Developer
3.4KViews
0likes
0Comments
Extract data from JSON URL
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
7 years ago Place Developer
Need Help
3.4KViews
0likes
2Comments

Data Privacy

Microsoft Fabric Community and Privacy

To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.