Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
DKlerk
New Member

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 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"

 

 

1 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

View solution in original post

3 REPLIES 3
DKlerk
New Member

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
Super User
Super User

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

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors