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
Stephanvk
Frequent Visitor

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:
2019-06-18_15-05-14.png

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.

1 ACCEPTED SOLUTION
Stephanvk
Frequent Visitor

Our in-house developer managed to get it fixed, posting this for other people who are struggling:
fix.png

View solution in original post

2 REPLIES 2
Stephanvk
Frequent Visitor

Our in-house developer managed to get it fixed, posting this for other people who are struggling:
fix.png

Stephanvk
Frequent Visitor

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.

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