Dear all,
Apologies, as i know this question has been asked before and their are several blogs regarding it, but not seem to match my exact scenario and after spending several dats researching i've not been able to solve.
Basically, i have an API that uses two parameters (Base_ID and Table_ID) to extract all records. To do this i followed this brilliant video and method https://www.youtube.com/watch?v=iiNDq2VrZPY. This works perfectly in PowerBI Desktop, but when it comes to scheduling a refresh it says Dynamic Data Sources aren't supported.
I have read that there is a work around, whereby you convert this to a web.contents, but honestly i'm very lost as tow where this can be applied and how, so any help would be greatly appreciated.
Below is the code behind the function, where i 'think' the changes are needed.
let
Source = (BASE_ID as text, TABLE_ID as text) => let
apiUrl = "https://api.airtable.com/v0/"&[BASE_ID]&"/"&[TABLE_ID]&"?Api_key=", token="xxxxxxxxx", options = [Headers=[#"Authorization"="Bearer " & token ]], result = Json.Document(Web.Contents(apiUrl , options)), records = result[records],
#"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "createdTime", "fields"}, {"Column1.id", "Column1.createdTime", "Column1.fields"}),
#"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"Start Date", "Progress", "Status", "Categories", "End Date", "Priority", "Leader", "All projects - Gantt"}, {"Column1.fields.Start Date", "Column1.fields.Progress", "Column1.fields.Status", "Column1.fields.Categories", "Column1.fields.End Date", "Column1.fields.Priority", "Column1.fields.Leader", "Column1.fields.All projects - Gantt"}),
#"Extracted Values" = Table.TransformColumns(#"Expanded Column1.fields", {"Column1.fields.Leader", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Column1.fields.All projects - Gantt", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
#"Extracted Values1"
in
Source
Thanks
sdg
