Forum Discussion

DKlerk's avatar
DKlerk
New Member
2 years ago
Solved

Refresh dynamic datasource

Hello everyone,    Is there someone who can help me changing my query so I can automatically refresh my report? I tried to follow this article Chris Webb's BI Blog: Using The RelativePath And Quer...
Need Help
  • DKlerk's avatar
    DKlerk
    2 years ago

    Got some help and this query solved the problem:

     

    let
    headers = [Authorization="xxxxxxxxx"],
    properties = "name,hubspot_owner_id,hs_parent_company_id,product_group,exact_debtor__debiteurnummer_",
    base = "https://api.hubapi.com/crm/v3/objects/companies",
    getOnePage = (optional after as nullable text) as record =>
    let
    queryOptions = [limit="100", properties=properties, archived="false"],
    queryOptionsWithAfter = if after <> null then Record.AddField(queryOptions, "after", after) else queryOptions,
    source = Json.Document(Web.Contents(base, [Headers=headers, Query=queryOptionsWithAfter])),
    data = source[results],
    nextAfter = try source[paging][next][after] otherwise null,
    morePages = nextAfter <> null
    in
    [Data=data, MorePages=morePages, After=nextAfter],
    getAllPages = List.Generate(
    () => getOnePage(null),
    each [Data] <> null,
    each if [MorePages] then getOnePage([After]) else [Data=null, MorePages=false, After=null],
    each [Data]
    ),
    combinedData = List.Combine(getAllPages),
    intoTable = Table.FromRecords(combinedData)
    in
    intoTable

sergej_og's avatar
sergej_og
Super User
2 years ago

Just my thoughts...
do you really need your "base"-line?

 - also not sure about "headers = [#"authorization" = "Bearer xxxxxx"]"
 - what about: Headers = [Authorization = "Bearer "]

 - maybe you have to adjust/put together other parts of relative parts with '&'

Looks very complex.

Regards

DKlerk's avatar
DKlerk
New Member
2 years ago

Got some help and this query solved the problem:

 

let
headers = [Authorization="xxxxxxxxx"],
properties = "name,hubspot_owner_id,hs_parent_company_id,product_group,exact_debtor__debiteurnummer_",
base = "https://api.hubapi.com/crm/v3/objects/companies",
getOnePage = (optional after as nullable text) as record =>
let
queryOptions = [limit="100", properties=properties, archived="false"],
queryOptionsWithAfter = if after <> null then Record.AddField(queryOptions, "after", after) else queryOptions,
source = Json.Document(Web.Contents(base, [Headers=headers, Query=queryOptionsWithAfter])),
data = source[results],
nextAfter = try source[paging][next][after] otherwise null,
morePages = nextAfter <> null
in
[Data=data, MorePages=morePages, After=nextAfter],
getAllPages = List.Generate(
() => getOnePage(null),
each [Data] <> null,
each if [MorePages] then getOnePage([After]) else [Data=null, MorePages=false, After=null],
each [Data]
),
combinedData = List.Combine(getAllPages),
intoTable = Table.FromRecords(combinedData)
in
intoTable