Relative Path to fix JSON import and allow auto refresh
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"