Refresh All Your Power BI Datasets with Python
Summary
TLDREste video muestra cómo actualizar todos los conjuntos de datos de Power BI en todos los espacios de trabajo con un solo clic de botón, utilizando un script de Python en segundo plano. El proceso implica conectarse a la API de Power BI, obtener todos los espacios de trabajo y conjuntos de datos y lanzar una actualización para cada uno. Para ejecutar el script, es necesario instalar Python, registrar una aplicación en Power BI y conceder permisos en Azure Portal. El script requiere la instalación de pandas y matplotlib para funcionar en Power BI. Finalmente, el script devuelve un marco de datos vacío para satisfacer los requisitos de Power BI.
Takeaways
- 🚀 Para actualizar todos los conjuntos de datos de Power BI en todos los espacios de trabajo con un solo clic, se ejecuta un script de Python en segundo plano.
- 🔗 El script de Python se puede copiar y pegar desde el blog post que acompaña este video.
- 💻 Para ejecutar el script, es necesario tener Python instalado en su máquina.
- 🔧 El primer paso es registrar una aplicación en Power BI con el tipo de aplicación nativa y obtener un ID de cliente.
- 🔓 En el portal de Azure, se deben conceder permisos de API para la aplicación registrada.
- 🔑 El script requiere su ID de cliente, correo electrónico y contraseña de Power BI para la autenticación.
- 🛠️ El script hace varias solicitudes a la API de Power BI para obtener workspaces, datasets y ejecutar actualizaciones.
- 📈 Se utiliza una estructura de datos de Pandas para satisfacer la necesidad de Power BI de devolver algún tipo de datos en el script.
- 🔄 El script actualiza todos los conjuntos de datos en todos los espacios de trabajo a los que tiene acceso.
- 🔌 Para ejecutar Python en Power BI Service en modo personal, se requiere una puerta de enlace de Power BI en su computadora.
- 📅 Ten en cuenta que cada actualización a través de la API de Power BI utiliza uno de sus actualizaciones programadas diarias.
Q & A
¿Qué es lo que Parker muestra en el video?
-Parker muestra cómo actualizar todos los conjuntos de datos de Power BI en todos los espacios de trabajo con un solo clic de botón, utilizando un script de Python en segundo plano.
¿Cómo se puede copiar el script de Python mostrado en el video?
-Puedes ir al blog post que acompaña este video y copiar el código directamente de allí.
¿Qué hace el script de Python en Power BI?
-El script de Python se conecta a la API de Power BI, obtiene todos los espacios de trabajo y los conjuntos de datos asociados, y luego inicia una actualización para cada conjunto de datos.
¿Qué es la primera herramienta que debes usar para ejecutar el script de Python?
-La primera herramienta que debes usar es la herramienta de registro de la API de Power BI, que puedes encontrar en dev.powerbi.com/apps.
¿Qué tipo de aplicación debes seleccionar al registrar la aplicación en la herramienta de registro de la API?
-Debes seleccionar el tipo de aplicación nativa (native) en lugar de aplicación web del lado del servidor (server-side web application).
¿Qué permisos son necesarios para la aplicación en Azure Portal?
-Necesitas permisos para leer todos los espacios de trabajo y leer y escribir todos los conjuntos de datos a través de la API.
¿Cómo autenticarse con la API de Power BI?
-Necesitas insertar tu ID de cliente, correo electrónico de Power BI y contraseña para autenticarte con la API y obtener un token de acceso.
¿Qué librerías son necesarias para ejecutar el script de Python en Power BI?
-Necesitas importar las librerías 'requests', 'json' y 'pandas'. Las primeras dos vienen por defecto en Python, mientras que 'pandas' necesita ser instalada previamente en tu máquina.
¿Qué sucede si necesitas ejecutar Python en Power BI Service en modo personal?
-Necesitarás instalar la puerta de enlace de datos de Power BI en modo personal en tu computadora, ya que actualmente no hay otra forma de ejecutar Python en Power BI Service fuera de través de una puerta de enlace personal.
¿Cómo se ve afectado el límite de actualizaciones diarias de Power BI con esta técnica?
-Cada actualización iniciada a través de la API de Power BI utiliza uno de tus límites diarios de actualización, por lo que debes tener en cuenta este límite al utilizar esta técnica.
¿Cómo se puede ver el resultado final de la actualización de conjuntos de datos en Power BI?
-Después de aplicar y ejecutar el script de Python en Power BI, se actualizarán todos los conjuntos de datos en los espacios de trabajo. Puedes verificar que se hayan actualizado visitando cualquier espacio de trabajo y verificando los conjuntos de datos.
Outlines
🚀 Actualización de conjuntos de datos de Power BI con un solo clic
Este párrafo presenta una guía para actualizar todos los conjuntos de datos de Power BI en todos los espacios de trabajo mediante un solo clic. Se discute la ejecución de un script de Python en segundo plano que actualiza los conjuntos de datos. El video muestra un ejemplo práctico de cómo funciona el proceso y se menciona la necesidad de instalar Python en la máquina para ejecutar el script. Además, se destaca la importancia de conectarse a la API de Power BI y se menciona un script de autenticación útil encontrada en la comunidad de Power BI.
🛠 Configuración y ejecución del script de Python en Power BI
Este segmento detalla los pasos para configurar y ejecutar el script de Python en Power BI. Se explica la necesidad de registrar una aplicación en Power BI para obtener un ID de cliente, conceder permisos de API y autenticarse con la cuenta de Power BI. Se mencionan las bibliotecas necesarias para el script, como requests, json y pandas, y se aclaran los cambios necesarios en el código para insertar credenciales y ejecutar la actualización de los conjuntos de datos. Finalmente, se describe cómo integrar el script en Power BI y su publicación en el servicio de Power BI.
📅 Manejo de actualizaciones y limitaciones diarias en Power BI
Este párrafo finaliza el video discutiendo la necesidad de un gateway de Power BI en modo personal para ejecutar scripts de Python en Power BI Service. Se menciona la limitación de ocho actualizaciones diarias y cómo el script utiliza una de estas. Además, se sugiere una estrategia para mantener los conjuntos de datos actualizados combinando actualizaciones programadas y manuales. El video termina con una recomendación para visitar el blog de BI Elite para obtener más información y consejos útiles sobre Power BI.
Mindmap
Keywords
💡Power BI
💡Python script
💡Workspaces
💡Datasets
💡API (Application Programming Interface)
💡Refresh
💡Single button click
💡Blog post
💡Azure portal
💡Client ID
💡Personal gateway
Highlights
Parker demonstrates a method to refresh all Power BI datasets across workspaces using a single button click.
The process involves running a Python script in the background.
The Python script connects to the Power BI REST API to refresh datasets.
The script retrieves all workspaces and datasets, then initiates a refresh for each dataset.
To run the script, Python must be installed on the machine.
The Power BI API registration tool is used to create an application for API access.
API permissions for reading workspaces and datasets are required.
The Azure portal is used to grant admin consent for the API.
The script needs the client ID, Power BI email, and password for authentication.
An access token is obtained for API communication.
The script sends requests to the Power BI API to get workspaces and datasets.
A POST request is used to refresh each dataset.
Power BI requires a pandas dataframe in the script for data return.
The script returns an empty dataframe to satisfy Power BI's requirement.
The method can be used in conjunction with scheduled refreshes.
Power BI Personal Gateway is necessary for running Python in Power BI Service.
The video provides a time-saving solution for refreshing multiple datasets.
Transcripts
what's up everybody its parker with VI
elite i have an awesome video today
showing you how to refresh all of your
power bi datasets across all of your
workspaces using a single button click
this is going to run a python script in
the background in order to refresh all
these datasets so for example I have
this workspace with a data set name to
refresh all data sets python script if i
click the refresh now' button you'll see
after a few seconds once it kind of
kicks in its going to refresh the other
three data sets in my view here
so now we see they're starting to
refresh and they're successfully
refreshed at 8:32 a.m. but the even
cooler idea here is i can go to all my
other workspaces like another workspace
and check out my data sets and you can
see these data sets were refreshed as
well so this is gonna refresh all of
your data sets across all of the
workspaces that you have access to so
this is an amazing trick and i'm gonna
show you how you can do in just a few
steps so the first thing you need to
understand about this trick is it's
running a python script
here's that python script if you want to
be able to just copy and paste this into
your own editor you can go ahead and
navigate over to the blog post
accompanying this video and copy it
directly from there so we'll get into
this code a little bit later but
basically what it's doing is it's
connecting to the power bi rest api
once it oughtn't Achatz it gets all of
the workspaces that you were a part of
and then all the data the data sets of
all of those workspaces and kicks off a
refresh for each one of those data sets
but we'll get into that a little bit
later so the first step in order to be
able to run this python script is
firstly installing python on your
machine i have another video showing you
how to install and run python scripts in
power bi so make sure you check out the
video I'll link it down in the
description as well so the first step
that we need to accomplish here in this
video is we need to go to the power bi
API registration tool so you can find it
at this link dev power bi comm slash
apps and basically it's a two step
process the first step is simply signing
in to power bi I've already done that
and the second step is registering your
applicant
so you firstly need to name your
application a name line refresh all
datasets and then this is very important
make sure you select the native
application type instead of server-side
web application you need to select
native and once you do so you just need
to provide a couple of API accesses we
only need to read all workspaces and
read and write all data set API accesses
once you do that click register and it's
going to create basically an application
or client ID so when that happens make
sure you save that I'm not actually
going to do that I've already done that
but save that application ID because
you're going to need to copy and paste
it into your code the next thing you
need to do is navigate over to your
Azure portal where you can grant your
permissions for the API so just starting
from the home page if you search for app
registrations so navigate there and
select your application that we just set
up so refresh all data sets and on the
left side there's an API permissions and
click this grant admin consent button
and it's gonna grant admin consent to be
used by your Python script do that and
your Python script will run flawlessly
so now open up a code editor take this
code that I've already read and so again
go over to the blog copy this code and
you're going to need need to change just
a couple of lines so you are going to
insert your client ID the one you just
got from that registration tool you're
gonna insert your power bi email and and
you're gonna insert it in your power bi
password these are necessary in order to
authenticate with that power bi API so
once you authenticate it's going to
grant you an access token and I do want
to say real quick I didn't write all of
this code for the authentication I found
this on a on a post on the power bi
community so props to the person that
wrote this code to authenticate I think
I've seen this around a couple of times
but it's very useful so once you get
that access token you then need to make
a series of requests to the power bi API
so the first request is going to get all
of your groups or workspaces so
basically using this power bi
end point to get your groups you're
gonna send that groups request they're
gonna come back in a kind of walkie
format but I'm using some logic to turn
it into the proper formatting in order
to get the information that we need I'm
not gonna walk through all of these
lines or all this logic but if you need
some more help kind of crunching through
this Python make sure you send me a
message over at BI elite comm slash
contact and I can you can send me a
message there and I'll reach back out to
you so once we have all of our
workspaces we're going to iterate
through those workspaces and send
another request to the power bi API in
order to retrieve all the datasets that
are that basically make up that
workspace and once we have all of our
datasets
we're gonna iterate through each of
those datasets and hit the power bi API
one more time to send a refresh for that
specific data set so as you can see just
in this refresh you are all at URL line
here we are sending a post request to
the specific endpoint and you see that
we're throwing in group and the dataset
ID that we had received previously from
those previous two calls so doing that
we're able to kick off a refresh for
each data set for each workspace that
I'm a member of and finally I should
note that the last line here it's
basically saying final equals pandas
dataframe this is only necessary because
power bi running a powered Python script
within power bi requires that a panda's
data frame is present at least once in
the script power bi needs a data frame
to be returned in order to basically say
this query returns some sort of data so
this is just returning an empty data
frame with no data but it satisfies
power bi eyes need for some data in that
Python script one more thing to note I'm
importing for libraries here up top a
dhow request JSON and pandas the first
three are built into Python by default
whereas pandas needs to be installed on
your machine before this will work again
that is covered over in my running
Python scripts in power bi video you
basically need to install both pandas
matplotlib in order to run Python
scripts at all in power bi so once you
have this once you fill out these three
lines up here client ID username and
password go ahead and copy that into
power bi so let's go ahead and navigate
over to power bi and I have opened up
the backend a power bi the query editor
and I have created a new source with a
Python script and simply pasted it in so
here is my script and you see I still
have those kind of dummy lines in so
this won't work for me so I'm actually
going to cancel out since I actually
have my real credentials stored in there
so cancel out or I'm gonna cancel out
you click OK let it run once you once it
runs select your final which is that
empty pandas dataframe that came through
and then finally it's gonna return an
empty table but just close and apply and
once we're back at our power bi report
once it's applied those changes and run
through that Python script it actually
will have refreshed all of your datasets
already since its kicked off that script
but in order to kind of see this in its
final working version I'm gonna go ahead
and save it once you save it to publish
it up to your power bi service portal
you can publish this anywhere I've
actually already published it to a
workspace called refresh example
so now I'd refresh example I'm going to
navigate there refresh example so I am i
refresh example now we're kind of back
to the beginning
once I click this refresh Now button as
you know is going to refresh all of my
datasets one thing you also need to know
is that you do need a power bi gateway
in personal mode installed on your
computer as of right now
there is no other way to run Python on
power bi service unless it's through a
personal gateway so just to show you I
have my personal gateway on-premises
data gateway personal mode already set
up on my computer
this is extremely necessary not very
hard to do just go simply just Google
for power bi personal mode gateway
there's some documentation showing you
how to set that up once you set that up
it should run well you'll click this
refresh Now button and you'll probably
get one
it's a one-time error saying you need to
provide credentials for this Python
script datasource
simply click sign-in and it'll just go
away and you'll be able to refresh as
many times as you want so just as an one
more example I'll click this refresh Now
button give it a little bit of time and
it's gonna kick off a refresh for every
single other data set in all of my
workspaces on my power bi portal so
these are starting to kick off and
finish you see this time has changed now
as we've gone through this video and
going back to our other workspace we see
these are now updated as well so I
really hope you like this video I'm
gonna use this trick all the time in
case I need to refresh all my data sets
at once instead of going through each
individual data set and clicking the
refresh Now button this can be used in
conjunction with the scheduled refresh
as well just keep in mind that you only
have eight refreshes per day so each one
of these refreshes that we're kicking
off via the power bi API is using one of
your scheduled refreshes so in reality
for my data sets if I need to keep them
really up to date I'll probably do
something like setting up six scheduled
refreshes per day and still myself to
click that button at least twice a day
in order to refresh all my data sets so
I really hope you like this trick make
sure you check out the blog over at the
bi elite blog has some really good stuff
over there and a little bit more
information on this trick so in case you
like this video make sure you subscribe
to the channel subscribe to the blog and
I will see you with a next trick
5.0 / 5 (0 votes)