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

[htacke]

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?

[v-shex-msft]

HI @htacke,

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)

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)

Regards,

Xiaoxin Sheng

[htacke]

This still doesnt solve my issue.

I can update in Desktop, but not after publishing

[v-shex-msft]

HI @htacke,

In fact, custom function only works on power bi desktop, power bi service not support this.

Please modify your function with query parameters.

Using the Power BI Service Parameters to change connection strings (To possibly change between Dev,

Regards,

Xiaoxin Sheng

[htacke]

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.

This is our query now:

================================

let
    // Definieer de gewenste parameters.
    urlApi = Url & "/odata/Buildings?$filter=Level ne " & Number.ToText(level) & " &orderby=Id &$select=Id,Code,Description,Level,InspectionDate,InspectionState,CVOagg",
    urlTokenApi = Url & "/Token",
    username = UserName,
    password = Password,
    level = Woningniveau,

    // Haal het WebApi bearer token op.
    TokenResult = Web.Contents(
        urlTokenApi,
        [
            Headers = [
                #"Accept" = "application/json",
                #"Content-Type" = "application/x-www-form-urlencoded"
            ],
            Content = Text.ToBinary ("grant_type=password&username=" & username & "&password=" & password)
        ]),
    FormatAsJson = Json.Document(TokenResult),
    AccessToken = FormatAsJson[access_token],
    AccessTokenHeader = "Bearer " & AccessToken,

    // Roep nu de WebApi aan met de verkregen bearer token.
    WebApiResult = Web.Contents(
        urlApi,
        [
            Headers = [
                #"Authorization"=AccessTokenHeader,
                #"Selected-Project"=Project
            ]
        ]),
    WebApiSource = Json.Document(WebApiResult),
    ResultList = WebApiSource[value],

    // Masseer hier de data voor verder gebruik.
    #"Converted to Table" = Table.FromList(ResultList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Id", "Code", "Description", "Level", "InspectionDate", "InspectionState", "CVOagg"}, {"Id", "Code", "Description", "Level", "InspectionDate", "InspectionState", "CVOagg"}),
    #"Inserted Parsed Date" = Table.AddColumn(#"Expanded Column1", "Parse", each Date.From(DateTimeZone.From([InspectionDate])), type date),
    #"Extracted Year" = Table.TransformColumns(#"Inserted Parsed Date",{{"Parse", Date.Year, Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Year",{{"Parse", "Inspectiejaar"}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"InspectionDate", each Text.BeforeDelimiter(_, "T"), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"InspectionDate", type date}}),
    #"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),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each true)
in
    #"Filtered Rows"

[htacke]

Still didn't get an answer.

do we need another solution?

[htacke]

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

let
 GetOdataPage = (authKey, project, Path) =>
  let
  Source = Json.Document(
   Web.Contents(
    Path,
    [
     Headers = [
      #"Authorization"=authKey,
      #"Selected-Project"=project
     ]
    ]
   )
  ),
  NextList=@Source[value],
  result = try @NextList & @GetOdataPage(authKey, project, Source[#"@odata.nextLink"]) otherwise @NextList
  in
  result,
 GetOdataToken = (url, username, password) =>
  let
  GetJson = Web.Contents(
   url,
   [
    Headers = [
     #"Accept" = "application/json",
     #"Content-Type" = "application/x-www-form-urlencoded"
    ],
    Content = Text.ToBinary ("grant_type=password&username=" & username & "&password=" & password)
   ]
  ),
  FormatAsJson = Json.Document(GetJson),
  AccessToken = FormatAsJson[access_token],
  AccessTokenHeader = "Bearer " & AccessToken
  in
  AccessTokenHeader,
 level = Woningniveau,
 urlApi = Url & "/odata/Buildings?$filter=Level ne " & Number.ToText(level) & " &orderby=Id &$select=Id,Code,Description,Level,InspectionDate,InspectionState,CVOagg", 
 urlTokenApi = Url & "/Token",
 username = UserName,
 password = Password,
 token = GetOdataToken(urlTokenApi, username, password),
 project=Project,
 ResultList = GetOdataPage(token, project, urlApi),
    #"Converted to Table" = Table.FromList(ResultList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Id", "Code", "Description", "Level", "InspectionDate", "InspectionState", "CVOagg"}, {"Id", "Code", "Description", "Level", "InspectionDate", "InspectionState", "CVOagg"}),
    #"Inserted Parsed Date" = Table.AddColumn(#"Expanded Column1", "Parse", each Date.From(DateTimeZone.From([InspectionDate])), type date),
    #"Extracted Year" = Table.TransformColumns(#"Inserted Parsed Date",{{"Parse", Date.Year, Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Year",{{"Parse", "Inspectiejaar"}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"InspectionDate", each Text.BeforeDelimiter(_, "T"), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"InspectionDate", type date}}),
    #"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),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each true)
in
    #"Filtered Rows"