Hi,
I have created BI report with API connection to datasource. Scheduled refresh on Service is worked fine few months. But suddenly the refresh stopped working and I got the error below:
| Data source error: | {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Information is needed in order to combine data"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"Web\",\"path\":\"https://XXXXXXXX.com/api/received-invoices/?created_at_after=01.01.2023\\u0026page=1\"},{\"kind\":\"SQL\",\"path\":\"XXXXX.domain.XXXX.si\\\\BI,1433;DATABASE\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"PrivacySetting"}}],"exceptionCulprit":1}}} Table: Invoices. |
When I make refresh in Power BI Desktop, everythink works ok.
I have checked if any changes was made on API side, but everythink stays like was. Also I did not make any changes in Power BI report.
I have already tried to publish the report again, I made a Query for the Invoices table again from the beginning but nothing helped.
Does anyone have any idea what could be wrong?
My Query for Invoices table:
let
body = "grant_type=Password&resource=https://XXXXXXX.com/api/auth/token&username=XXXXXXXX&password=XXXXXXXXX",
Data = Json.Document(Web.Contents("https://XXXXXXX.com/api/auth/token/", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)])),
access = Data[access],
CurrentstartAtList = List.Generate(()=> [Result = try Json.Document(Web.Contents("https://XXXXXXX.com/api/received-invoices/", [RelativePath = "?created_at_after=01.01.2023&page="&Number.ToText(Page), Headers=[Authorization="Bearer " & access]])) otherwise null, Page=1],
each [Result]<>null,
each [Result = try Json.Document(Web.Contents("https://XXXXXXXX.com/api/received-invoices/", [RelativePath = "?created_at_after=01.01.2023&page="&Number.ToText(Page), Headers=[Authorization="Bearer " & access]])) otherwise null, Page=[Page]+1], each [Result]),
#"Converted to Table" = Table.FromList(CurrentstartAtList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Result" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"results"}, {"results"}),
#"Expanded results" = Table.ExpandListColumn(#"Expanded Result", "results"),
#"Expanded results1" = Table.ExpandRecordColumn(#"Expanded results", "results", {"id", "file_name", "url", "file", "status", "confirmed_at", "confirmed_by", "rejection", "rejected_by", "created_at", "document_type", "finished_at"}, {"id", "file_name", "url", "file", "status", "confirmed_at", "confirmed_by", "rejection", "rejected_by", "created_at", "document_type", "finished_at"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded results1",{{"file_name", type text}, {"url", type text}, {"file", type text}, {"status", type text}, {"rejection", type text}, {"rejected_by", type text}, {"confirmed_by", type text}, {"document_type", type text}, {"confirmed_at", type datetimezone}, {"created_at", type datetimezone}, {"finished_at", type datetimezone}, {"id", Int64.Type}}),
#"Inserted Time Subtraction" = Table.AddColumn(#"Changed Type", "Subtraction", each [finished_at] - [confirmed_at], type duration),
#"Inserted Days" = Table.AddColumn(#"Inserted Time Subtraction", "Days", each Duration.Days([Subtraction]), Int64.Type),
#"Inserted Hours" = Table.AddColumn(#"Inserted Days", "Hours", each Duration.Hours([Subtraction]), Int64.Type),
#"Inserted Minutes" = Table.AddColumn(#"Inserted Hours", "Minutes", each Duration.Minutes([Subtraction]), Int64.Type),
#"Inserted Seconds" = Table.AddColumn(#"Inserted Minutes", "Seconds", each Duration.Seconds([Subtraction]), type number),
#"Changed Type3" = Table.TransformColumnTypes(#"Inserted Seconds",{{"Seconds", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type3", "Duration", each ([Days]*24*3600)+([Hours]*3600)+([Minutes]*60)+[Seconds]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Subtraction", "Days", "Hours", "Minutes", "Seconds"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Removed Columns1",{{"Duration", Int64.Type}, {"confirmed_at", type date}, {"created_at", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type4", {"id"}, fGlavePRvFA, {"WEB_ExtID"}, "fGlavePRvFA", JoinKind.LeftOuter),
#"Expanded fGlavePRvFA" = Table.ExpandTableColumn(#"Merged Queries", "fGlavePRvFA", {"IntStRac", "OrgStRac", "Dobavitelj", "DobavNaz", "DatOrgRac", "Konto", "DatKnjiz", "ZnesekRac", "Lastnik", "Baza", "DatVnosa"}, {"IntStRac", "OrgStRac", "Dobavitelj", "DobavNaz", "DatOrgRac", "Konto", "DatKnjiz", "ZnesekRac", "Lastnik", "Baza", "DatVnosa"}),
#"Added Custom1" = Table.AddColumn(#"Expanded fGlavePRvFA", "EnteredBookedTime", each if [status]="FI" then [DatKnjiz]-[DatVnosa] else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "CreateBookedTime", each if [status]="FI" then [DatKnjiz]-[created_at] else null),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "CreateConfirmTime", each if [status]="EX" or [status]="FI" then [confirmed_at]-[created_at] else null),
#"Changed Type5" = Table.TransformColumnTypes(#"Added Custom3",{{"EnteredBookedTime", Int64.Type}, {"CreateBookedTime", Int64.Type}, {"CreateConfirmTime", Int64.Type}})
in
#"Changed Type5"
