Hi All,
After much research and reading a lot of posts I was able to use a base code and adapt it to my PBi needs, the current code merges all pages on a JSON but I am unable to refresh the database automatically due to PBi detecting the authoring query being already dynamic.
I read that using [Relative Path = ] fixes this problem, however every time that I try it I get different type of errors. Would anyone be so kind as to fix my current code or help me understand how should I face this problem?
Many many thanks,
let
BaseUrl = #"API URL",,
ApiKey = #"API Key",
EntitiesPerPage = 10000,
GetJson = (Url) =>
let Options = [
Headers = [
#"accept" = "application/json"
]
],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = BaseUrl & "&$count=true&$top=0",
Json = GetJson(Url),
Count = Json[#"@odata.count"]
in Count,
GetPage = (Index) =>
let Skip = "&$skip=" & Text.From(Index * EntitiesPerPage),
Top = "&$top=" & Text.From(EntitiesPerPage),
Url = BaseUrl & Skip,
Json = GetJson(Url),
Value = Json[#"value"]
in Value,
EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"Id", "Department", "LoginName", "Title", "Email", "IsDeleted", "UserFieldValues"}, {"Id", "Department", "LoginName", "Title", "Email", "IsDeleted", "UserFieldValues"}),
#"Expanded UserFieldValues" = Table.ExpandListColumn(#"Expanded Column1", "UserFieldValues"),
#"Expanded UserFieldValues1" = Table.ExpandRecordColumn(#"Expanded UserFieldValues", "UserFieldValues", {"Value"}, {"UserFieldValues.Value"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded UserFieldValues1",{{"UserFieldValues.Value", "EDM_ID"}})
in
#"Renamed Columns"
