As it says in the title, I'm trying to pull a large dataset via an API and after getting the query working I can't figure out why it's still reading as dynamic after using RelativePath & Options. This takes almost 40 mintues to refresh so I'm hoping someone can help me out with the query and explain what it happening for this to be read as dynamic.
I have the main Query that pulls a list of IDs from the system, and then a function that gets called on that list of IDs to pull records related to that ID from the same API
let
BaseUrl = "https://api.site.com/odata/v2/User?$filter=location eq 'company'&$select=userId&$format=json",
Token = "TOKEN",
Options = [Headers=[ #"Authorization" = "Basic " & Token ]],
MyJsonRecord = Json.Document(Web.Contents(BaseUrl, Options)),
Result = MyJsonRecord,
d = Result[d],
Count = Record.FieldCount(d),
FirstNextUrl = try d[__next] otherwise null,
GetNextData = (Url) as record =>
let
NextRecord = Json.Document(Web.Contents(Url, Options)),
d = try NextRecord[d] otherwise null,
data = [ results = try d[results] otherwise null,
__next = try d[__next] otherwise null,
count = try Record.FieldCount(d) otherwise 0 ]
in
data,
FullList = List.Generate( () =>
[ i = 2000, NextUrl = GetNextData(BaseUrl)],
each [i] <> 0,
each [i = [NextUrl][count] , NextUrl = GetNextData([NextUrl][__next])],
each [NextUrl] ),
#"Converted to Table" = Table.FromList(FullList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
..., // Expanding Columns and such here...
#"Expanded Column1.results1" = Table.ExpandRecordColumn(#"Expanded Column1.results", "Column1.results", {"userId"}, {"Column1.results.userId"}),
#"Invoked Custom Function" = Table.AddColumn(#"Expanded Column1.results1", "Learning Hours", each GetLearningHours([Column1.results.userId])),
The customer function called here is as follows
(user) =>
let
Token = "TOKEN=",
content1= "{
""grant_type"":""client_credentials"",
""scope"":{
""userId"":""USERID"",
""companyId"":""COMPANYID"",
""userType"":""admin"",
""resourceType"":""some_api""
}
}",
Options = [Headers=[ #"Authorization" = "Basic " & Token ], Content = Text.ToBinary(content1)],
BaseUrl = "https://comp.site.com/path/oauth-api/rest/v1/token",
Source = Json.Document(Web.Contents(BaseUrl, Options)),
Result = Source,
tk = Result[access_token],
userf = "'" & user & "'",
Options2 = [Headers=[ #"Authorization" = "Bearer " & tk ], RelativePath="path/odatav4/public/user/user-service/v1/historys?$filter=criteria/targetUserID eq " & (userf)],
BaseUrl2 = "https://comp.site.com/",
Source2 = Json.Document(Web.Contents(BaseUrl2, Options2))
in
Source2
I'm really flailing around at this point, so any insight or direction here would be helpful. It isn't clear to me what is dynamicly generated at this point that isn't captured in relative path.
