Forum Discussion
Failed to save modifications to the server when calling API
I have a similar problem and fix it to solve it NOT using the feaure preview "Store datasets using enhanced metadata format"
Thanks for replying but unfortunately neither of these solutions helped in my case.
Any other ideas?
Cheers
Hi Anonymous ,
I just came across this same problem when just messing around with some public info with the below M script. Not sure why this erroring as it loads the data ok in power query. Sorry I can't answer your problem but maybe someone can answer this one which may be the same as your problem.
let Now1 = DateTime.LocalNow(), Date1 = DateTime.ToText(Now1,"yyy-MM-dd"), Source = Excel.Workbook(Web.Contents("https://www.ecdc.europa.eu/sites/default/files/documents/COVID-19-geographic-disbtribution-worldwide-" & Date1 & ".xlsx"), null, true), #"COVID-19-geographic-disbtributi_Sheet" = Source{[Item="COVID-19-geographic-disbtributi",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"COVID-19-geographic-disbtributi_Sheet", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"DateRep", type date}, {"Day", Int64.Type}, {"Month", Int64.Type}, {"Year", Int64.Type}, {"Cases", Int64.Type}, {"Deaths", Int64.Type}, {"Countries and territories", type text}, {"GeoId", type text}, {"Pop_Data.2018", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Pop_Data.2018", "Pop_Data_2018"}}) in #"Renamed Columns"I am also seeing the same problem. This really looks & feels like a bug.
This query works:
let //textmonth=Number.ToText(3), yesterday="0"&"3"&"-26-2020", url="https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/"&yesterday&".csv", Source = Csv.Document(Web.Contents(url),[Delimiter=",", Columns=12, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each yesterday), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"FIPS", Int64.Type}, {"Admin2", type text}, {"Province_State", type text}, {"Country_Region", type text}, {"Last_Update", type datetime}, {"Lat", type number}, {"Long_", type number}, {"Confirmed", Int64.Type}, {"Deaths", Int64.Type}, {"Recovered", Int64.Type}, {"Active", Int64.Type}, {"Combined_Key", type text}}) in #"Changed Type"This following query results in an error. Note that I replaced "3" with textmonth which equals Number.ToText(3). This query should yeild an identical result but it actually results in an error.
let textmonth=Number.ToText(3), yesterday="0"&textmonth&"-26-2020", url="https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/"&yesterday&".csv", Source = Csv.Document(Web.Contents(url),[Delimiter=",", Columns=12, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each yesterday), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"FIPS", Int64.Type}, {"Admin2", type text}, {"Province_State", type text}, {"Country_Region", type text}, {"Last_Update", type datetime}, {"Lat", type number}, {"Long_", type number}, {"Confirmed", Int64.Type}, {"Deaths", Int64.Type}, {"Recovered", Int64.Type}, {"Active", Int64.Type}, {"Combined_Key", type text}}) in #"Changed Type"Likegooranga1, the preview loads fine. It is once I close the query editor and attempt to refresh data that I see the same error.
Hi Anonymous ,
Could you please provide the source code for every query in Advanced Editor? Is there any warning icon in front of every query? Need to check the data source of every query, and if they refer any invalid data...
Best Regards
Rena
Hi Anonymous
Thanks for replying, please find my advanced query below (I XXXXX'd out my apikey and Url):
You could basically do the same thing with any paged api call.
let
BaseUrl = "https://api.XXXXXXXXX.com/v1/bookings?",
EntitiesPerPage = 10,
GetJson = (Url) =>
let Options = [Headers=[apiKey="XXXXXXXXXXXXXXXXXXXXXXXX"]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Count = 20
in Count,
GetPage = (Index) =>
let Offset = "&offset=" & Text.From(Index * EntitiesPerPage),
Limit = "&limit=" & Text.From(EntitiesPerPage),
Url = BaseUrl & Offset & "&" & Limit,
Json = GetJson(Url),
Value = Json[#"bookings"]
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", {"orderNumber", "status", "supplierId", "supplierName", "supplierAlias", "createdBy", "customer", "items", "totalAmount", "totalCurrency", "totalPaid", "totalDue", "dateCreated", "dateConfirmed", "datePaid", "comments", "internalNotes", "payments", "fields", "source", "sourceChannel", "resellerComments", "vouchers", "resellerReference"}, {"Column1.orderNumber", "Column1.status", "Column1.supplierId", "Column1.supplierName", "Column1.supplierAlias", "Column1.createdBy", "Column1.customer", "Column1.items", "Column1.totalAmount", "Column1.totalCurrency", "Column1.totalPaid", "Column1.totalDue", "Column1.dateCreated", "Column1.dateConfirmed", "Column1.datePaid", "Column1.comments", "Column1.internalNotes", "Column1.payments", "Column1.fields", "Column1.source", "Column1.sourceChannel", "Column1.resellerComments", "Column1.vouchers", "Column1.resellerReference"})
in
#"Expanded Column1"Cheers
