Refresh dynamic datasource

[DKlerk]

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 Query Options With Web.Contents() In Power Query An... but still need some help. 

Thanks in advance!

Query:

let
headers = [#"authorization" = "Bearer xxxxxx"],
base = "https://api.hubapi.com/crm/v3/objects/companies?limit=100&properties=name&properties=hubspot_owner_i...",
baseuri = Web.Contents("https://api.hubapi.com/",[RelativePath="crm/v3/objects/companies", Query =[limit="100",properties="name,hubspot_owner_id,hs_parent_company_id,product_group,exact_debtor__debiteurnummer_",archived="false"], Headers=headers]),
initReq = Json.Document(baseuri),
initData = initReq[results],

//We want to get data = {lastNPagesData, thisPageData}, where each list has the limit # of Records,

//then we can List.Combine() the two lists on each iteration to aggregate all the records. We can then

//create a table from those records

gather = (data as list, uri) =>

let

//get new offset from active uri
newOffset = Json.Document(Web.Contents(uri ,[Headers=headers]))[paging][next][after],

//build new uri using the original uri so we dont append offsests
newUri = base & "&after=" & newOffset,

//get new req & data
newReq = Json.Document(Web.Contents(newUri ,[Headers=headers])),
newdata = newReq[results] ,

//add that data to rolling aggregate
data = List.Combine({data, newdata}),

//if theres no next page of data, return. if there is, call @gather again to get more data

check = if Table.Contains ( Record.ToTable(newReq) , [Name = "paging"] ) = true then @gather (data , newUri) else data

in check,

//before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
outputList = if Table.Contains ( Record.ToTable (initReq) , [Name = "paging"] ) = true then gather( initData , base ) else initData ,

//then place records into a table. This will expand all columns available in the record.
expand = Table.FromRecords(outputList),
#"Expanded properties" = Table.ExpandRecordColumn(expand, "properties", {"createdate", "exact_debtor__debiteurnummer_", "hs_lastmodifieddate", "hs_object_id", "hs_parent_company_id", "hubspot_owner_id", "metalwire_sector", "name", "product_group"}, {"createdate", "exact_debtor__debiteurnummer_", "hs_lastmodifieddate", "hs_object_id", "hs_parent_company_id", "hubspot_owner_id", "metalwire_sector", "name", "product_group"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded properties", {"hs_parent_company_id"}, #"Expanded properties", {"id"}, "Expanded properties", JoinKind.LeftOuter),
#"Expanded Expanded properties" = Table.ExpandTableColumn(#"Merged Queries", "Expanded properties", {"name"}, {"name.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Expanded properties",{{"name.1", "Parent Company Name"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"id", "createdate", "exact_debtor__debiteurnummer_", "hs_lastmodifieddate", "hs_object_id", "hs_parent_company_id", "Parent Company Name", "hubspot_owner_id", "name", "product_group", "createdAt", "updatedAt", "archived"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "product_group", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"product_group.1", "product_group.2", "product_group.3", "product_group.4"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"product_group.1", type text}, {"product_group.2", type text}, {"product_group.3", type text}, {"product_group.4", type text}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"product_group.1", "product_group.2", "product_group.3", "product_group.4"}, "Attribute", "Value"),
#"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Value", "Product group"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Attribute"})
in
#"Removed Columns"

[DKlerk]

Thank you for you quick response @sergej_og. 

The header seems ok but I think the issue is within the baseuri indeed, gonna try to avoid this line. 

[sergej_og]

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 · Accepted solution]

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