How to daily refresh my dataset when using odata with token authetication

Question

I have made a dashboard with odata that is authenticated in de Mquery with a token. In PowerBI Desktop i can refresh this data whenever i want.

If i publish it, I publish it with this data and want it refreshed overnight. Now i get an error message: 'Query contains unknown or unsupported data sources'.

How can i make this refrefresh to work?

anonymous · Answer

HI&nbsp;htacke,
&nbsp;
I think you can add new steps at query editor before your 'get data' function.(add variable step to store token which generated from token api)
&nbsp;
Then you can use this variable as parameter in your 'get data' method, so you not need worry about token timeout.(each time you refresh, the variable will get new token string from generate token method)
&nbsp;
Regards,
Xiaoxin Sheng
&nbsp;

htacke · Answer

I use the following query to get data;In the desktop it refreshes fine, but i dont get it refreshed automatically when i publish it; i want it to refresh daily&nbsp;let&nbsp;GetOdataPage = (authKey, project, Path) =&gt;&nbsp;&nbsp;let&nbsp;&nbsp;Source = Json.Document(&nbsp;&nbsp;&nbsp;Web.Contents(&nbsp;&nbsp;&nbsp;&nbsp;Path,&nbsp;&nbsp;&nbsp;&nbsp;[&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Headers = [&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#"Authorization"=authKey,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#"Selected-Project"=project&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;]&nbsp;&nbsp;&nbsp;&nbsp;]&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;),&nbsp;&nbsp;NextList=@Source[value],&nbsp;&nbsp;result = try @NextList &amp; @GetOdataPage(authKey, project, Source[#"@odata.nextLink"]) otherwise @NextList&nbsp;&nbsp;in&nbsp;&nbsp;result,&nbsp;GetOdataToken = (url, username, password) =&gt;&nbsp;&nbsp;let&nbsp;&nbsp;GetJson = Web.Contents(&nbsp;&nbsp;&nbsp;url,&nbsp;&nbsp;&nbsp;[&nbsp;&nbsp;&nbsp;&nbsp;Headers = [&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#"Accept" = "application/json",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#"Content-Type" = "application/x-www-form-urlencoded"&nbsp;&nbsp;&nbsp;&nbsp;],&nbsp;&nbsp;&nbsp;&nbsp;Content = Text.ToBinary ("grant_type=password&amp;username=" &amp; username &amp; "&amp;password=" &amp; password)&nbsp;&nbsp;&nbsp;]&nbsp;&nbsp;),&nbsp;&nbsp;FormatAsJson = Json.Document(GetJson),&nbsp;&nbsp;AccessToken = FormatAsJson[access_token],&nbsp;&nbsp;AccessTokenHeader = "Bearer " &amp; AccessToken&nbsp;&nbsp;in&nbsp;&nbsp;AccessTokenHeader,&nbsp;level = Woningniveau,&nbsp;urlApi = Url &amp; "/odata/Buildings?$filter=Level ne " &amp; Number.ToText(level) &amp; " &amp;orderby=Id &amp;$select=Id,Code,Description,Level,InspectionDate,InspectionState,CVOagg",&nbsp;&nbsp;urlTokenApi = Url &amp; "/Token",&nbsp;username = UserName,&nbsp;password = Password,&nbsp;token = GetOdataToken(urlTokenApi, username, password),&nbsp;project=Project,&nbsp;ResultList = GetOdataPage(token, project, urlApi),&nbsp;&nbsp;&nbsp; #"Converted to Table" = Table.FromList(ResultList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),&nbsp;&nbsp;&nbsp; #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Id", "Code", "Description", "Level", "InspectionDate", "InspectionState", "CVOagg"}, {"Id", "Code", "Description", "Level", "InspectionDate", "InspectionState", "CVOagg"}),&nbsp;&nbsp;&nbsp; #"Inserted Parsed Date" = Table.AddColumn(#"Expanded Column1", "Parse", each Date.From(DateTimeZone.From([InspectionDate])), type date),&nbsp;&nbsp;&nbsp; #"Extracted Year" = Table.TransformColumns(#"Inserted Parsed Date",{{"Parse", Date.Year, Int64.Type}}),&nbsp;&nbsp;&nbsp; #"Renamed Columns" = Table.RenameColumns(#"Extracted Year",{{"Parse", "Inspectiejaar"}}),&nbsp;&nbsp;&nbsp; #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"InspectionDate", each Text.BeforeDelimiter(_, "T"), type text}}),&nbsp;&nbsp;&nbsp; #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"InspectionDate", type date}}),&nbsp;&nbsp;&nbsp; #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "InspectieStatus", each if [InspectionState] = "None" then "Geen" else if [InspectionState] = "Active" then "Inspectie actief" else if [InspectionState] = "Scheduled" then "Gereed voor inspectie" else if [InspectionState] = "Ready" then "Inspectie gereed" else null),&nbsp;&nbsp;&nbsp; #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each true)in&nbsp;&nbsp;&nbsp; #"Filtered Rows"

htacke · Answer

This still doesnt solve my issue.I can update in Desktop, but not after publishing

anonymous · Answer

HI&nbsp;htacke,
&nbsp;
In fact, custom function only works on power bi desktop, power bi service not support this. 
&nbsp;
Please modify your function with query parameters.
Using the Power BI Service Parameters to change connection strings (To possibly change between Dev,
&nbsp;
Regards,
Xiaoxin Sheng

htacke · Answer

We removed the custom functions in the query, but still not able to refresh in powerbi Service.We have no datasource on the report; the connection string is in the query.&nbsp;This is our query now:================================let&nbsp;&nbsp;&nbsp; // Definieer de gewenste parameters.&nbsp;&nbsp;&nbsp; urlApi = Url &amp; "/odata/Buildings?$filter=Level ne " &amp; Number.ToText(level) &amp; " &amp;orderby=Id &amp;$select=Id,Code,Description,Level,InspectionDate,InspectionState,CVOagg",&nbsp;&nbsp;&nbsp; urlTokenApi = Url &amp; "/Token",&nbsp;&nbsp;&nbsp; username = UserName,&nbsp;&nbsp;&nbsp; password = Password,&nbsp;&nbsp;&nbsp; level = Woningniveau,&nbsp;&nbsp;&nbsp; // Haal het WebApi bearer token op.&nbsp;&nbsp;&nbsp; TokenResult = Web.Contents(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; urlTokenApi,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Headers = [&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; #"Accept" = "application/json",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; #"Content-Type" = "application/x-www-form-urlencoded"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Content = Text.ToBinary ("grant_type=password&amp;username=" &amp; username &amp; "&amp;password=" &amp; password)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ]),&nbsp;&nbsp;&nbsp; FormatAsJson = Json.Document(TokenResult),&nbsp;&nbsp;&nbsp; AccessToken = FormatAsJson[access_token],&nbsp;&nbsp;&nbsp; AccessTokenHeader = "Bearer " &amp; AccessToken,&nbsp;&nbsp;&nbsp; // Roep nu de WebApi aan met de verkregen bearer token.&nbsp;&nbsp;&nbsp; WebApiResult = Web.Contents(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; urlApi,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Headers = [&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; #"Authorization"=AccessTokenHeader,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; #"Selected-Project"=Project&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ]),&nbsp;&nbsp;&nbsp; WebApiSource = Json.Document(WebApiResult),&nbsp;&nbsp;&nbsp; ResultList = WebApiSource[value],&nbsp;&nbsp;&nbsp; // Masseer hier de data voor verder gebruik.&nbsp;&nbsp;&nbsp; #"Converted to Table" = Table.FromList(ResultList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),&nbsp;&nbsp;&nbsp; #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Id", "Code", "Description", "Level", "InspectionDate", "InspectionState", "CVOagg"}, {"Id", "Code", "Description", "Level", "InspectionDate", "InspectionState", "CVOagg"}),&nbsp;&nbsp;&nbsp; #"Inserted Parsed Date" = Table.AddColumn(#"Expanded Column1", "Parse", each Date.From(DateTimeZone.From([InspectionDate])), type date),&nbsp;&nbsp;&nbsp; #"Extracted Year" = Table.TransformColumns(#"Inserted Parsed Date",{{"Parse", Date.Year, Int64.Type}}),&nbsp;&nbsp;&nbsp; #"Renamed Columns" = Table.RenameColumns(#"Extracted Year",{{"Parse", "Inspectiejaar"}}),&nbsp;&nbsp;&nbsp; #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"InspectionDate", each Text.BeforeDelimiter(_, "T"), type text}}),&nbsp;&nbsp;&nbsp; #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"InspectionDate", type date}}),&nbsp;&nbsp;&nbsp; #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "InspectieStatus", each if [InspectionState] = "None" then "Geen" else if [InspectionState] = "Active" then "Inspectie actief" else if [InspectionState] = "Scheduled" then "Gereed voor inspectie" else if [InspectionState] = "Ready" then "Inspectie gereed" else null),&nbsp;&nbsp;&nbsp; #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each true)in&nbsp;&nbsp;&nbsp; #"Filtered Rows"&nbsp;

Forum Discussion

How to daily refresh my dataset when using odata with token authetication

6 Replies

Related content

Refresh dataset api

dataset refresh

Refreshing dataset

Dataset refresh error - timeout

Refresh Power BI Dataset using Power Automate