Forum Discussion

zoubeyrB's avatar
zoubeyrB
Frequent Visitor
1 year ago

Can't schedule refresh as Web.Content is used

Hello,

I've been trying to connect a Power BI report to gitlab to extract data related to current issues. The Report uses the following query :

SRC : (base_url, rel_url_part1 and token_value are parameters)

 

 

let
    BaseURL_1 = base_url,
	URL_1 = rel_url_part1,
        Source_1 = Web.Contents(BaseURL_1,[RelativePath = URL_1,Query = [state="all",scope = "all",per_page = "100",page = "1"],Headers=[Authorization=token_value]]),
	JsonDoc_1 = Json.Document(Source_1),
    table_1 = Table.FromList(JsonDoc_1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
     expand = Table.ExpandRecordColumn(table_1, "Column1", {"id", "iid", "project_id", "title", "description", "state", "created_at", "updated_at", "closed_at", "closed_by", "labels", "milestone", "assignees", "author", "type", "assignee", "user_notes_count", "merge_requests_count", "upvotes", "downvotes", "due_date", "confidential", "discussion_locked", "issue_type", "web_url", "time_stats", "task_completion_status", "has_tasks", "task_status", "_links", "references", "severity", "moved_to_id", "service_desk_reply_to"}, {"id", "iid", "project_id", "title", "description", "state", "created_at", "updated_at", "closed_at", "closed_by", "labels", "milestone", "assignees", "author", "type", "assignee", "user_notes_count", "merge_requests_count", "upvotes", "downvotes", "due_date", "confidential", "discussion_locked", "issue_type", "web_url", "time_stats", "task_completion_status", "has_tasks", "task_status", "_links", "references", "severity", "moved_to_id", "service_desk_reply_to"}),
    filter = Table.SelectRows(expand, each Text.Contains([web_url], "xxxxx")),
    expand_columns = Table.ExpandRecordColumn(filter, "closed_by", {"id", "name"}, {"closed_by.id", "closed_by.name"}),
    expand_columns2 = Table.ExpandListColumn(expand_columns, "labels"),
    expand_columns3 = Table.ExpandListColumn(expand_columns2, "assignees"),
    expand_columns4 = Table.ExpandRecordColumn(expand_columns3, "assignees", {"id", "name"}, {"assignees.id", "assignees.name"}),
    expand_columns5 = Table.ExpandRecordColumn(expand_columns4, "author", {"id", "name"}, {"author.id", "author.name"}),
    expand_columns6 = Table.ExpandRecordColumn(expand_columns5, "assignee", {"id", "name"}, {"assignee.id", "assignee.name"}),
    expand_columns7 = Table.ExpandRecordColumn(expand_columns6, "references", {"full"}, {"references.full"}),
    remove_columns = Table.RemoveColumns(expand_columns7,{"user_notes_count", "merge_requests_count", "upvotes", "downvotes", "confidential", "discussion_locked", "time_stats", "task_completion_status", "has_tasks", "task_status", "_links", "references.full", "moved_to_id", "service_desk_reply_to"})

in
remove_columns

 

 

I get to refresh data locally, but when I publish to the workspace I get this error :

Scheduled refresh is disabled because at least one data source is missing credentials

 

Could you kindly help me find the origine of this error ?

 

Thanks in advance,

 

"Power Query"
"Refresh"

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,SamWiseOwl 
    Hello,zoubeyrB .I am glad to help you.

    Regarding power BI GitLab connect to GitLab data source, I hope my suggestion below is helpful.

    Generally speaking, power bi connect to web data generally use web connection method, and you also have to specify the correct data source credentials(token)


    For Power BI Service, if the data source of the report comes from a third party website, you generally need to create a gateway connection to ensure that the data is secure and valid.
    When you enter the data source credentials in the gateway you are actually validating and protecting the data. If you successfully configure a gateway connection, the power bi service will automatically monitor the authentication methods carried in the gateway and generate your data source credentials. (This step is achieved to ensure that the data source connects properly and you can perform a refresh)

    I noticed that the token is actually provided in the M code you provided.
    Here are a few common authentication modes

    1.OAuth2
    Scenario: for situations where you need to authenticate with an access token, e.g. connecting to GitLab, SharePoint Online etc.
    Advantages: High security, supports automatic token refresh.
    2.Basic Authentication
    Scenario: For cases that require authentication by user name and password, such as connecting to traditional databases or Web services.
    Advantages: simple configuration, but lower security.
    3. Anonymous
    Scenario: for public data sources that do not require authentication.
    Benefits: No credentials to configure.
    Power bi service connecting to a web data source will have the problem of expired data source credentials (token).
    The solution to this problem is to update the token regularly.
    You can try to automate this process using the Python + Power BI RestAPI approach
    Write a Python script to periodically fetch new web tokens and update the data source credentials using the Power BI REST API.
    This requires some programming skills, and you may need to reach out to programmers on your team to make this happen.
    About  Python +API
    Python     accessToken

    Solved: Re: access token power bi python - Microsoft Fabric Community

    The above can also be combined with dataflow to make the whole process more standardized and you only need to maintain dataflow. (suggestions provided by SamWiseOwl)

    I noticed that Microsoft has provided a connector on Power BI to GitLab, hope that helps.
    Microsoft Azure Marketplace
    Get data from git lab to power BI for analysis - Microsoft Fabric Community
    [How To] Connect to Web Data Sources in Power BI – Go Analytics

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Carson Jian,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.     



    • zoubeyrB's avatar
      zoubeyrB
      Frequent Visitor
      1 year ago

      Thanks for your answer SamWiseOwl ,

       

      Unfortunately, I don't have access to the "Data source credentials" section (the only datasource is the web page).

       

      • SamWiseOwl's avatar
        SamWiseOwl
        Super User
        1 year ago

        That would suggest the connection is being treated as a local data source.

        When I connect directly to some data sources it uses Web.BrowserContents which is connecting to the data through my browser hence a local source. Changing it to Web.Contents fixes this issue as it become an online data source.

         

        The lazy fix for this is installing a Gateway and connecting through that.

        A gateway lets Power BI Service connect to the data source through your computer.

        For example connecting to a SQL database on a server or an Excel file on your desktop.

        Sadly if you install one then the refresh will only happen when your computer is turned on.

         

        The harder part will be working out why it is treating your online data source as a local one!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,zoubeyrB .I am glad to help you.

    Has your problem been solved?
    If you have found suitable solutions, please share them as it will help more users with similar problems.
    For example, relevant code or links to articles.
    Or you can mark the valid suggestions provided by other users as solutions.
    Thank you very much for your understanding and support of Power BI.

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Carson Jian,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.