Refresh Dataset with Dynamic Web Query with API Token

[Morrison]

Hello,

I ask you for help because with this set of Dynamic Data I cannot reload the data automatically.

How can I improve the Query to make it possible to schedule the upload?

     let

    BaseUrl         =   https://xxx.xxx.com/api/v3/forms/hash/entries.json?,

    CountUrl         = https://xxx.xxx.com/api/v3/forms/hash/entries/count.json?,

    Token           = "xxxx- xxxx - xxxx - xxxx ",

    EntitiesPerPage = 100,

    GetJson = (Url) =>

        let

            RawData = Web.Contents(Url), //, Options),

            Json    = Json.Document(RawData)

        in  Json,

    GetEntityCount = () =>

        let Url   = CountUrl, //& "$count=true&$top=0",

            Json  = GetJson(Url),

            Count = Json[#"EntryCount"]

        in  Count,

    GetPage = (Index) =>

        let Skip  = "&pageStart=" & Text.From(Index * EntitiesPerPage),

            Top   = "&pageSize=" & Text.From(EntitiesPerPage),

            Url   = BaseUrl & Skip & Top,

            Json  = GetJson(Url),

            //Value = Json

            Value = Json[#"Entries"]

        in  Value,

    EntityCount = List.Max({ EntitiesPerPage, Number.FromText(GetEntityCount()) }),

    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),

    PageIndices = { 0 .. PageCount - 1 },

    Pages       = List.Transform(PageIndices, each GetPage(_)),

    Entries     = Pages[Entries],

    Entities    = List.Union(Pages),

    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Tabella Column1 espansa" = Table.ExpandRecordColumn(Table, "Column1", {"EntryId", "Field1", "Field2", "Field36", "Field58", "Field39", "Field57", "Field34", "Field42", "Field12", "Field43", "Field13", "Field50", "Field60", "Field24", "Field63", "Field51", "Field52", "Field65", "Field53", "Field280", "Field483", "Field381", "DateCreated", "CreatedBy", "DateUpdated", "UpdatedBy"}, {"EntryId", "Field1", "Field2", "Field36", "Field58", "Field39", "Field57", "Field34", "Field42", "Field12", "Field43", "Field13", "Field50", "Field60", "Field24", "Field63", "Field51", "Field52", "Field65", "Field53", "Field280", "Field483", "Field381", "DateCreated", "CreatedBy", "DateUpdated", "UpdatedBy"}),

    //#"Rinominate colonne" = Table.RenameColumns(#"Tabella Column1 espansa",{{"EntryId", "Entry ID"}, {"Field1", "Nome"}})

    #"Renamed Columns"      = Table.RenameColumns(#"Tabella Column1 espansa",Table.ToRows(#"Nome Query"),MissingField.Ignore),

    #"Rinominate colonne" = Table.RenameColumns(#"Renamed Columns",{{"Field2", "Cognome"}, {"Nome e Cognome", "Nome"}}),

    #"Testo inserito tra i delimitatori" = Table.AddColumn(#"Rinominate colonne", "Seleziona PDV", each Text.BetweenDelimiters([#"Seleziona PDV#(lf)"], "[", "]"), type text),

    #"Modificato tipo" = Table.TransformColumnTypes(#"Testo inserito tra i delimitatori",{{"Date Created", type datetime}}),

    #"Modificato tipo1" = Table.TransformColumnTypes(#"Modificato tipo",{{"Date Created", type date}}),

    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo1", "CompanyCode", each "TE"),

    #"Query accodata" = Table.Combine({#"Aggiunta colonna personalizzata", #"Nome Query 2", #"Nome Query 3"}),

    #"Filtrate righe" = Table.SelectRows(#"Query accodata", each true)

in

    #"Filtrate righe"

//in

   // #"Rinominate colonne"

Thx All

[ImkeF · Accepted solution]

Hi @Morrison ,
this article contains some methods to apply for your case: Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI Chris Webb'...