Dear Friends,
I have an Odata Link from which i retrieve records from Business Central. The M Query i use to get all 2,5 million records finally works in PowerBI Desktop. It does work in the preview of powerbi online to. However, when i try to publish i get the following Error: One or more tables refer to a dynamic data source. The M Query below is what i use.
There are several blogs online wich seem to adress this issue, but i cant get anyone of them to work. Can anyone help?
https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...
Paramter for the BaseUrl: https://api.businesscentral.dynamics.com/v2.0/9558-623452d0dbf2/Test/api/tcg/bi/v2.0/companies(ee11-...
let
// Function to fetch data from a single page URL
FetchPage = (url as text) as list =>
let
// Fetch the JSON document from the URL
Fetch = Json.Document(Web.Contents(url)),
// Extract the data from the response
Data = Fetch[value]
in
Data,
// Base URL parameter
BaseUrl = BaseUrl,
// Reference to the BaseUrl parameter
// Number of records per page
PageSize = 20000,
// Generate the list of URLs for pagination
GenerateUrls = List.Transform({0..999}, each BaseUrl & "?" & "$skip=" & Text.From(PageSize * _) & "&$top=" & Text.From(PageSize)),
// Function to fetch data until there are no more records
FetchAllData = List.Generate(
() => [PageIndex = 0, Data = FetchPage(GenerateUrls{0})],
each List.Count([Data]) > 0 and [PageIndex] < List.Count(GenerateUrls),
each [PageIndex = [PageIndex] + 1, Data = FetchPage(GenerateUrls{[PageIndex]})],
each [Data]
),
// Combine all fetched data
AllData = List.Combine(FetchAllData),
// Convert the list to a table and expand the entryNo column
ResultTable = Table.FromList(AllData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(ResultTable, "Column1", {"entryNo"}, {"entryNo"}),
#"Kolommen transformeren" = Table.TransformColumnTypes(#"Expanded Column1", {{"entryNo", type text}}),
#"Fouten vervangen" = Table.ReplaceErrorValues(#"Kolommen transformeren", {{"entryNo", null}})
in
#"Fouten vervangen"
