Refresh All Your Power BI Datasets with Python

Hey there Power BI fans! Wouldn’t it be nice if you could refresh all of your datasets over all of your workspaces with a single click of a button? This tutorial will show you how to make this happen! With this technique, you will be able to refresh every single one of your datasets by executing a Python script housed in a single Power BI dataset. Before we get started, let’s see the magic in action

The meat of the logic necessary to perform this technique will be contained in our Python script. The script will need to accomplish several tasks. Firstly, we must authenticate through the Power BI REST API to ensure we are who we say we are. Second, we retrieve a list of the workspaces available to us. Finally, we retrieve a list of datasets contained in each group and kick off a refresh for each dataset. We’ll come back to this logic when we write the script, I just wanted to explain the general flow.

If you’re wondering what the Power BI REST API is or what it can be used for, take a look at the documentation (https://docs.microsoft.com/en-us/rest/api/power-bi/). In short, you can use it to perform a large number of functions programmatically using HTTP requests. For example, if I want to retrieve a list of all of the Power BI datasets in “My Workspace,” there is an API endpoint for that information.

Before doing anything else, we need to register our future tool to use the Power BI API. Navigate to https://dev.powerbi.com/apps and fill out the form like such. Firstly, sign into the proper Power BI account. Give your application a name and select “Native” for the application type. Finally, check the boxes next to “Read all workspaces” and “Read and write all datasets” and click Register.

One more housekeeping item… Even though we have registered our application, we need to grant it permissions to use. Navigate to https://portal.azure.com/#blade/Microsoft_AAD_RegisteredApps/ApplicationsListBlade and select your newly created app. Navigate to the “API permissions” screen on the left-hand side and click the “Grant admin consent” button.

Nice, now let’s write our Python script. I’m not going to walk through each line individually, but feel free to reach out to me via https://bielite.com/#contact if you need a deeper explanation of the logic.

import adal
import requests
import json
import pandas

authority_url = 'https://login.windows.net/common'
resource_url = 'https://analysis.windows.net/powerbi/api'
client_id = 'INSEERT_YOUR_CLIENT_ID'
username = 'YOUR_POWERBI_EMAIL'
password = 'YOUR_POWERBI_PASSWORD'
context = adal.AuthenticationContext(authority=authority_url,
                                     validate_authority=True,
                                     api_version=None)
token = context.acquire_token_with_username_password(resource=resource_url,
                                                     client_id=client_id,
                                                     username=username,
                                                     password=password)

access_token = token.get('accessToken')

groups_request_url = 'https://api.powerbi.com/v1.0/myorg/groups'

header = {'Authorization': f'Bearer {access_token}'}

groups_request = json.loads(requests.get(url=groups_request_url, headers=header).content)

groups = [d['id'] for d in groups_request['value']]

for group in groups:
    datasets_request_url = 'https://api.powerbi.com/v1.0/myorg/groups/' + group + '/datasets'
    datasets_request = json.loads(requests.get(url=datasets_request_url, headers=header).content)
    datasets = [d['id'] for d in datasets_request['value']]
    for dataset in datasets:
        refresh_url = 'https://api.powerbi.com/v1.0/myorg/groups/' + group + '/datasets/' + dataset + '/refreshes'
        r = requests.post(url=refresh_url, headers=header)

final = pandas.DataFrame()

Lines 1-4 simply import the packages necessary to run our code. Luckily, most of these packages are come built into to Python by default. In order to run this code in Power BI, you just need to install the Pandas and Matplotlib libraries. Check out my quick video on running Python scripts in Power BI for quick instructions (https://www.youtube.com/watch?v=EmALxO7V7y0). Authentication through the Power BI API is handled by lines 6-19, resulting in an “access_token” to be used by our subsequent API calls. Note that you must insert your client id, Power BI email, and Power BI password into their respective lines of code to make this work for you. Lines 21-27 return the list of available workspaces. Next, lines 29-35 loop through each workspace, retrieve the available datasets, and kick off a refresh of each individual dataset. Finally, line 37 creates and empty Pandas DataFrame. This only needs to be done because Power BI needs a DataFrame to be returned for the query to run.

At this point, we actually have a fully working solution if you want to run this code on a Python interpreter. In the next couple steps, we are going to house this Python script in a Power BI file so that we can run this code directly from Power BI Service. Open up Power BI Desktop and create a new query using a Python script and insert in our code from above.

Click OK and let the script run. When the dialog box opens, select “final” to load the empty DataFrame from line 37 of our script to satisfy Power BI’s need for data. When complete, it will have actually refreshed all of your datasets for you! Simply close and apply your changes, save your file, and publish it up to Power BI Service.

The only task remaining to ensure that we can kick off this Python script on PowerBI.com is to install a Power BI Data Gateway in Personal Mode. This is extremely important as Python scripts can only be run on Power BI Service through the use of a personal gateway at the moment. To download and configure the gateway, follow this documentation: https://docs.microsoft.com/en-us/power-bi/service-gateway-personal-mode.

With that taken care of, all I have to do is kick off a refresh of my dataset containing the Python script! You might get a one-time error asking you to provide credentials for the Python script data source. Simply clicking the sign-in button alleviates the issue. And now for the final reveal… I will click the refresh button one more time and watch the magic unfold!

All of our datasets have been refreshed! Navigating to another workspace, I can see that these datasets were refreshed too!

Now you have a single Power BI dataset that can kick off the refresh of every other dataset that you have access to across all of your Power BI workspaces, even datasets and workspaces that have yet to be created

#python #data-science

What is GEEK

Buddha Community

Microsoft Power BI Consulting | Power BI Solutions in India

Hire top dedicated Mirosoft power BI consultants from ValueCoders who aim at leveraging their potential to address organizational challenges for large-scale data storage and seamless processing.

We have a team of dedicated power BI consultants who help start-ups, SMEs, and enterprises to analyse business data and get useful insights.

What are you waiting for? Contact us now!

No Freelancers, 100% Own Staff
Experienced Consultants
Continuous Monitoring
Lean Processes, Agile Mindset
Non-Disclosure Agreement
Up To 2X Less Time

##power bi service #power bi consultant #power bi consultants #power bi consulting #power bi developer #power bi development

Hire Power BI Developer | Microsoft Power BI consultants in India

Hire our expert Power BI consultants to make the most out of your business data. Our power bi developers have deep knowledge in Microsoft Power BI data modeling, structuring, and analysis. 16+ Yrs exp | 2500+ Clients| 450+ Team

Visit Website - https://www.valuecoders.com/hire-developers/hire-power-bi-developer-consultants

#power bi service #power bi consultant #power bi consultants #power bi consulting #power bi developer #power bi consulting services

top 30 Python Tips and Tricks for Beginners

Welcome to my Blog , In this article, you are going to learn the top 10 python tips and tricks.

1) swap two numbers.

2) Reversing a string in Python.

3) Create a single string from all the elements in list.

4) Chaining Of Comparison Operators.

5) Print The File Path Of Imported Modules.

6) Return Multiple Values From Functions.

7) Find The Most Frequent Value In A List.

8) Check The Memory Usage Of An Object.

…

#python #python hacks tricks #python learning tips #python programming tricks #python tips #python tips and tricks #python tips and tricks advanced #python tips and tricks for beginners #python tips tricks and techniques #python tutorial #tips and tricks in python #tips to learn python #top 30 python tips and tricks for beginners

Power BI In Brief – 2020

Every month, we bring you news, tips, and expert opinions on Power BI? Do you want to tap into the power of Power BI? Ask the Power BI experts at ArcherPoint.

Power BI Desktop – Feature List
More exciting updates for August—as always:

• Reporting - Perspectives support for Personalize visuals; rectangular lasso-select for data points; additional dynamic formatting support to more visuals
• Analytics - Direct Query support for Q&A
• Visualizations - Linear Gauge by xViz; advanced Pie & Donut by xViz; ratings visual by TME AG; toggle switch by TME AG; fdrill down Pie PRO by MAQ Software; ADWISE RoadMap; updates to ArcGIS Maps; extending Admin capabilities for AppSource visuals
• Template Apps - Agile CRM analytics for Dynamics 365
• **Data Preparation ** - Text/CSV By Example
• Data connectivity - Cherwell connector; Automation Anywhere connector; Acterys connector

To get in-Depth knowledge on Power BI you can enroll for a live demo on Power BI online training

Power BI Developer Update
And the updates continue—this time, for developers:

• Updates in embedded analytics
• Automation & life-cycle management
• New API for updating paginated reports data sources
• Get dataset/s APIs return new additional properties
• Embed capabilities
• Persistent filters support for embedding in the organization
• Phased embedding
• Control focus behavior for create/clone visual
• Additional Javascript API enhancements
• Selected learning resources

Multiple Data Lakes Support For Power BI Dataflows
And if that’s not enough, Microsoft also announced improvements and enhancements to Azure Data Lake Storage Gen2 support inside Dataflows in Power BI. Improvements and enhancements include: Support for workspace admins to bring their own ADLS Gen2 accounts; improvements to the Dataflows connector; take-ownership support for dataflows using ADLS Gen2; minor improvements to detaching from ADLS Gen2. Changes will start rolling out during the week of August 10. Read more on multiple data lakes support in Power BI dataflows.

To get more knowledge of Power BI and its usage in the practical way one can opt for Power bi online training Hyderabad from various platforms. Getting this knowledge from industry experts like IT Guru may help to visualize the future graphically. It will enhance skills and pave the way for a great future.

#power bi training #power bi course #learn power bi #power bi online training #microsoft power bi training #power bi online course

Is Power BI Actually Useful?

The short answer, for most of you, is no. However, the complexity and capability of the products could be beneficial depending on what type of position or organization you work in.

In my effort to answer this common question about Power BI I researched the following:
– Power BI Desktop Gateway
– Syncing on-prem SQL server data
– Syncing SharePoint Online list data
– Syncing data from an Excel workbook
– Building, and sharing a dashboard
– Inserting a Power BI visualization into PowerPoint

To get in-Depth knowledge on Power BI you can enroll for a live demo on Power BI online training

The feature spread above gave me the opportunity to explore the main features of Power BI which break down as:
– Ingesting data, building a data set
– Creating dashboard or reports with visualizations based on that data

In a nutshell Power BI is a simple concept. You take a data set, and build visualizations that answer questions about that data. For example, how many products have we sold in Category A in the last month? Quarter? Year? Power BI is especially powerful when drilling up or down in time scale.
And there are some interesting ways to visualize that data:
However, there are a number of drawbacks to the current product that prevented me from being able to fold these visualizations into our existing business processes.

1. Integration with PowerPoint is not free. This shocked me.

The most inspiring Power BI demo I saw at a Microsoft event showed a beautiful globe visualization within a PowerPoint presentation. It rendered flawlessly within PowerPoint and was a beautiful, interactive way to explore a geographically disparate data set. I was able to derive conclusions about the sales data displayed without having to look at an old, boring chart.

During the demo, nothing was mentioned about the technology required to make this embedded chart a reality. After looking into the PowerPoint integration I learned that not only was the add-in built by a third party, it was not free, and when I signed up for a free trial the add-in could barely render my Power BI visualization. The data drill up/down functionality was non-existent and not all of the visualizations were supported. Learn more from Power bi online course

2. Only Dashboards can be shared with other users, and cannot be embedded in our organization’s community on SharePoint.

Folks in our organization spent 50% of their time in Outlook, and the rest in SharePoint, OneNote, Excel, Word, and the other applications needed for producing documents, and other work. Adding yet another destination to that list to check on how something is doing was impossible for us. Habits are extremely hard to change, and I see that consistently in our client’s organizations as well.

Because I was not able to fold in the visualizations with the PowerPoint decks we use during meetings, I had to stop presentations in the middle, navigate to Internet Explorer (because the visualizations only render well in that browser), and then go back to PowerPoint once we were done looking at the dashboard.

This broke up the flow of our meetings, and led to more distractions. I also followed up with coworkers after meetings to see if they ever visited the dashboard themselves at their desk. None of them had ever navigated to a dashboard outside of a meeting.

3. The visualizations aren’t actually that great.

Creating visualizations that cover such a wide variety of data sets is difficult. But, the Excel team has been working on this problem for over 15 years. When I import my SharePoint or SQL data to Excel I’m able to create extremely customized Pivot Tables and Charts that show precisely the data I need to see.

I was never able to replicate visualizations from Excel in Power BI, to produce the types of visualizations I actually needed. Excel has the ability to do conditional formatting, and other customizations in charts and tables that is simply not possible with Power BI. Because of how generic the charts are, and the limited customization it looks “cool” without being functional.

In conclusion, if you have spare time and want to explore Power BI for your organization you should. However, if you are seriously thinking about how you can fold this product into your work processes, challenge yourself to build a dashboard and look at it once a week. See if you can keep that up for a month, and then think about how that change affected your work habits and whether the data analysis actually contributed value each time. At least half of you will realize that this gimmicky product is fancy, but not actually useful.

Take your career to new heights of success with Power BI online training Hyderabad

#power bi training #power bi course #learn power bi #power bi online training #microsoft power bi training #power bi online course