The below query working fine
but when I try to save and Close the dataflow it throughs an error
Can't save dataflow "One or more tables references a dynamic data source
let Source = URLTable,
FirstURL = Source{0}[URL], // Change 1 to the index of the URL you want to use
// Function to handle retry with exponential backoff
RetryFunction = (URL) =>
let
// Define the number of retry attempts
maxAttempts = 5,
// Initial wait time in milliseconds
initialWait = 1000,
// Function to make the actual request
MakeRequest = (retryCount, waitTime) =>
let
// Attempt to fetch data
result = try GoogleSheets.Contents(URL)
otherwise null,
// Handle retry logic
nextWait = waitTime * 2
in
if result <> null or retryCount >= maxAttempts then
result
else
MakeRequest(retryCount + 1, nextWait)
in
MakeRequest(1, initialWait),
// Invoke the retry function with the FirstURL
Tb = RetryFunction(FirstURL),
// Navigate and process data within the dynamically fetched table (Tb)
Navigation = Tb{[name = "Members", ItemKind = "Table"]}[Data],
PromotedHeaders = Table.PromoteHeaders(Navigation, [PromoteAllScalars = true]),
ChangedColumnType = Table.TransformColumnTypes(PromotedHeaders, {
{"Membership Number", type text},
{"Membership ID", Int64.Type},
{"Membership Category", Int64.Type},
{"Category Name", type text},
{"Member Status", type text}
})
in
ChangedColumnType
