>> Good morning, everyone, good afternoon, and good evening. Whatever your time zone may be. Thank you so much for joining us
for today's Power BI MVP Webinar, brought to you by Gil Raviv, who is one of our experts
here in the MVP Community. He's a blogger at DataChant. He's the Director of
Analytics at Avanade. He does some great work there. Of course, Avanade is an exceptional company when
it comes to analytics. I don't know if you know
a little bit about Gil, but Gil is an author. He's a blogger. He used to work
at Microsoft, which we love. He's also led the design and
integration of Power Query over here. He also has been a devoted M Practitioner since the M equals Power
Query formula language. He's got over 20 years of software development experience
and four US patents. He's a brain. We learn from him every day. Welcome Gil. How are you? >> Hi. Thank you Kelly so
much for having me today. I'm really excited to have this
session today with you guys. This is really exciting. On my days working as part of the
Power BI/Excel team Microsoft. I became so in love with
these technologies that I decided to stop developing them and start using
them and teach others. So really amazing technology,
amazing solutions, and I'm excited to be here today to talk and share with you some of the amazing
capabilities of Power BI. In specific to our strange situation, starting this year with
the COVID-19 pandemic. I was thinking as most of us
are bound to work from home, or are starting gradually
returning to work, I thought it would be best for
our community to learn how to use Power BI to build your
own COVID-19 dashboard. This is what we are going
to do today and also in next session Webinar that
you will have next month. Really excited to be here today to go through many different
demonstrations of capabilities. With the idea to just
give you more knowledge, more technical tools to build
your own analysis on COVID-19. Does it make sense? Shall we start? >> Yeah, I think so.
This is fantastic. I'm super excited about
this and I think this was a featured one, as well Gil. We featured this on our
Power BI Community. >> Yeah, absolutely. That was I
thinking on April, I released. So just to let you know
and we will start out with demos with this link
that you see here. You can actually find on AppSource. If you see my browser. This is on Microsoft AppSource. You know the official app store
for any business applications, including Power BI applications, and you probably know this source
also for custom visualizations. But in this case, what you can
see if you will go through this link and if you don't find it, I'm not sure if you can, I can try to paste it to you. But I recommend you
just do a Google search for AppSource DataChant and
you will find this resource. What I want to show you at the
beginning of our session today, how you can use this free tool as an entry point for building
your own COVID-19 report. Later on after this
part of the session, that we'll focus more on how
you can use this solution as your infrastructure for analyzing COVID-19 and build your
own reports on top of it. I will also start showing
you what you need to do to actually build your
own report without relying on all these
work that we have here. As a starting point, we can click here on
the "Get it now". Here you are accepting Microsoft permissions to install
this specific Power BI app, which takes you basically
into your Power BI service. Inside your Power BI service, you can decide to install. Let's call this one AppSource COVID-19 and I
will add here some AAA, just so I'll see it at the
beginning of my work spaces, and I click "Install". You may see I have it on my DataChant demo environment for Power BI bunch
of different demos, including here one that is exactly the same that
I installed early on. But I just wanted to show you
live the end-to-end experience, so you will see how easy it is for you to get for free this template up and start using it within
your own organization. As you can see now here, this specific application
was installed. I can click here on "Go to App". This is important. When you want to do
analysis on COVID-19, it's not enough to have
analysis on old data. You want to be able to
have a solution and dataset that keeps
refreshing every day as you get new updates from the authorities regarding
confirmed cases and such. Here you see, I will
just click "Sign in". In a second, you'll see
the rotating window. Here you see the rotating windows
for the dataset being refreshed. Within a minute, we will
have seen now everything. The refresh was done. You can also, after
you install the app, you can go under settings of the data source and even set
the schedule refresh to work. So every day you will
get new data sources, new data coming in. Now, we'll move to the analysis
part for this solution. But before we do, I wanted
to make sure that we give the proper credit
about the data source. You can say, Okay Gil, you built this amazing dashboard, but can you tell us what
are your data sources? Are they reliable? For that regard, I have built the solution based on the John
Hopkins University data sources. So to give them the
right credit here, because this is one of the most reliable sources
to track COVID-19 cases. Used also by the media and by
many different organizations. All the solutions that I built
is based on this data source. You can see here below recently they also changed the license agreement. In the past, I could
only share with you this solution for learning
purposes as we do today. If you would need now to use the dashboard for an official
enterprise-grade solution, let's say for managing the return to work policies in your organization using Power BI and Power Platform, there are many great Microsoft tools that are now available also on AppSource for return
to work engagements, automation's, and managing different
elements for your company. Because this data source is now using the Creative
Commons Attribution, you are welcome to use this
data source also for business, for commercial purposes,
and not just for learning. Of course I'm not your legal
advisor for your company, so you better get also
an official approval. I think it's a great change in terms of the data
source.In the past, it was only for learning. Back to our application
after you installed it. You make the refresh. You will now see under the app sections that I have
this specific workspace, this specific app workspace, ready for me for the ability
to start doing the analysis. Here we will not go into details of every little nuance that
we have in this solution. But what I wanted to
share with you is that this is available for you for free. You can start using it. The good news is for example, let's say you see here you
have a logo for my blog. Perhaps for your
enterprise-grade solution, you don't want to use my logo. It's easy to go in, click "Edit", not here on the app, but on the workspace. Just to show you, like
any Power BI app. This is not a session about
Power BI apps in general. You may be able on the
Microsoft official website, learn more about Power
BI template apps. This is another template
app like many others. Like any template app, what you can do with it, is to go, and you can see once
you install the template app, you have two variations of it. The first variation is a workspace. So you see under workspaces, I can click here on the workspace. This is where the
development team that owns the COVID-19 dashboard
can make changes. It is where you will make
all kinds of changes. For example, I will go to the report, again in the workspace environment, and I can now go to the
ellipsis, click "Edit", and for example, decide
to remove my logo. I will be very sad if you do
that, but you can do that. You can put your company logo
instead and go and click "Save", and everything is ready for you to go back on the workspace
and click Update app. Clicking "Update" in the app, this one will allow you to share
the app within your organization. Under the app section, you can see we have another entity. This entity is not
for the developers, this is for the
application consumers. Your organization can start
enjoying this report. You can see that the
DataChant logo is off. You don't have it anymore here. You can build any customization
using the browser, configure the report as you see fit. Before we move to the
next element here, I wanted to tell you a bit, everything that you see here today. Many of the technical elements of it can be found on my
blog, datachant.com. You are ready to follow specifically, if you'll go to datachant.com
with the tag of COVID-19. This specific link is available
to you in the logo on that, after that I just removed. You will see here many tutorials. Every article here is the
tutorial step-by-step, showing you capabilities
of how to load the data, either from John Hopkins
University data source or from World Health Care, so the World Health Organization, WHO, and be able to
build your own reports, some articles about Power Query
and the data combinations, transformation elements
of that challenge you. Some articles that are about
DAX and depth and time intelligence and what if scenarios and even very
complex calculations. For example, in this article, we are offsetting
different trend lines, different curves of
COVID-19 to start by the relative day with COVID-19 started for each one of the
states or the countries. All this ability to do very
advanced calculations, is not just available to
you on the AppSource, it's also available
to you as a tutorial, that you can learn how
to build it yourself. Let's move back to our app, and I wanted to show you, let's say now you want to expose this free application
to your business and allowing them to start consuming
it and build perhaps even, their own solution on top of it. Let's say you are form IT. You don't have the resources
and the bandwidth to build an IT sanction COVID-19 report. But you would like
just to allow them to certify the data set and allow any business to
build their own report, every business may have
different return to work, policies and business needs. You don't really want to spend too much time in building
the User experience, you just want to provide them the ability to
build the own reports. For that reason, let's just, to show you here I will
go to the workspaces. I will go back to my workspace, this is again, my
development environment. I can now, let's say I'm on the IT side or I have some permission
to also to certify datasets. I can go to the datasets
level here, okay. Click here under settings, and here under settings you can see, I have the endorsement
section allowing me to say; either to promote the data
set or to certify it. Promoted is probably like the
Power BI champions within your organizations that
have enough obtaining, enough knowledge,
enough understanding of the governance policies within your organization and
they will be allowed. You can set the permissions
on the admin tenant settings, then you will be allowed
to promote these datasets. IT can then get the next level
and even certify the datasets, so here I can say, "This is the Johns
Hopkins data source. Can be used for business
scenarios by the different, let's say by Marketing
and Finance teams." I wrote this as an explanation for all the marketing and business team that are a part of and may have
permissions to use the app, now I can click "Apply". Now you can see if I will go now, let's close this browser and
open a clean Power BI desktop. Now let's say I'm from the finance team opening
the Power BI desktop. I can go now to get
data and select "Power BI datasets" and in under
the Power BI datasets, you can find out that I had
COVID-19 certified that I can use, coming from the workspace of AAA AppSource COVID-19 that we
just refreshed nine minutes ago. That's the same data set you just certified and you can now use it, so let's just go again and
do that. I will select. I clicked the wrong place, sorry, just one second, so get data. I clicked by mistake Power BI
dataflows until Power BI datasets. Now I'm on the datasets. I am selecting this
certified datasets. Click "Create", and you'll see immediately using
the Power BI live connection, my new Power BI desktop
is connected with the COVID-19 app that I
just shared with you. Now, just as an example, I can select the COVID-19 cases. Let's take here, you see
all the different measures. You can, even by the way, and this is a relatively a
new feature in Power BI, connecting live to a
different data set. You can click on the
"Model view" to actually see the relationships
that I have in the model. You can of course understand all, every US population, connected with one to many relationship
to the US COVID-19, and all these kind of more advanced exploration to
the data is available also,now, in this case, my scenario to the finance or marketing team
that can access this data source. Now, it's just as an example. Let's select confirmed cases. We'll take the total confirmed
cases and we'll select the date. Will take the date, and now you can see in
this specific visual, I can now use the hierarchy for the time and drill down till
I get to the lowest level. Let's change it into a line chart, so you see this is worldwide
confirmed cases, accumulated numbers. Let's change it into a daily one, so I'm searching for the daily, and I have every daily new cases. I can bring it here. Now you'll see some fluctuations. That is the daily. I can also, copy paste this one here and change the daily into
a running average of five days, and now you can see that the line
is being a bit like smoother. We have here some
kind of a blank line that probably it's a bag on my side. But if I will change
the date field to, instead of an hierarchy, just to use the dates, you can see that they will
have the same experience. In this line as the
daily fluctuations, this one is using a five-day running average that
shows you that information edit. With less noise that can
come from an arbitrary, different detection, diagnosis meant testing
policies in a specific country. For this capability again, this is a Power BI
desktop, I used it, built from scratch, using a
[inaudible] database data, set that we have on our tenant. Just because you installed the DataChant template up
for for COVID-19 analysis. It's a great way for
you to start building all kinds of interesting tools. You can either build your own things, even create new measures in DAX
of your new Power BI desktop. Or you can go here
under the workspace, and inside the workspace you
can go and select the report. Click here on the "Edit". Again, this is the workspace and this is the development environment. I can click here to add a new page. Let's, for example,
bring small multiples. I can go here and there
or get more visuals. Connect now to AppSource, to bringing custom visuals. Let's search for, "small multiples". We have a actually a nice custom
visual that is certified, you see this local blue ribbon, so it's certified custom visual. Now including it in the report. Now in a second you're going to see, we have now this specific icon
that was just added here. We can add it to the report, let's expand to do it. Now I can select the date, assign the date to the axis. Under the small multiples, I can select the country, let's bring the country here. Now let's take, for example the daily cases. Now I can bring the daily cases. Let's see the daily new cases. This is the one that I want to use. In 30 seconds or so,
this visual renouncing. Now it shows me the small multiples
for each one of the countries. I can also sort it by the daily
new cases and make it descending. So the first country with
the highest number of daily new cases will be shown
here as the felt small multiple. Now you see it's relatively slow. I did work on a new version of this Power BI report that will
be published on AppSource. I hope very soon that will give you more functionality that
is not available today, but will be available hopefully
within the next week. So just to give you an example here. Here you have also in
this Power BI report, you have also the ability to
click here on small multiples. So this is the version
for small multiples. In this case, you can see now that California and Texas, and Florida, and Georgia, are the top countries with the highest daily new cases. We can also see the timeline
for the daily new cases and see which state is it in
terms of the cases. I can also flip to a
global visual so India is felt highest number of daily
cases today than Brazil than US. If for example, you are located in the United States and you want
perhaps to see your favorite state, let's say in my case
I am in Illinois. So I can click here on Illinois. Now I am going to see
at the bottom part that Cook County has the highest
number of daily cases today. It makes sense because the
majority of the population in Illinois is around Chicago
and Cook County is Chicago. So just to give you an
example, and of course, we can also go unselect Cook
County, unselect Illinois. Now we can get, we can actually see it. So imagine that you are
working in a global company where you have many different
offices all around US. Here you can see which counties
in US across the entire US, just one second, let's unselect it. Here we can see, for example, that Riverside, which is part of California, has the highest daily cases. Today, if we have an office
in Riverside, California, but absolutely need to do
some making sure that we have all our temperature testing tools and all the cleaning
supplies for our employees. That office and these kind of capabilities will allow you
to build such solutions without wasting too much time on all the data preparation and all the modeling and
measurements such. So now I think it's a perfect timing, we crossed the half
of our session today. I thought it would be a
perfect timing after I showed you what you can do in terms of using the existing Power BI app
on AppSource that I published, how to do everything from scratch without relying on
anything on my side. Kelly, are there any questions
that we should take at this stage? >> Yeah, we do. We have
a couple of questions. I think Elie said the analyzed COVID-19 cases
out require Power BI license. I'm sorry, a Power BI
Pro license to access? >> That's a good question. The answer is yes. Any Power BI app
available on AppSource, or even if it's not
available in AppSource, requires as part of the instantiation
and installation of it, that you will have permission
to create a workspace. You can only have the permission to create a workspace if you have
a Power BI report license. >> Awesome. Then Salvador asked, will it be possible to have the
access for this Power BI file. The non-pro users can't get the app. Thanks in advance. >> Not at this stage, just because it is a lot of work invested here
and if I may share it, I will have a lot of support
and maintenance to do. However, if you will follow my blog, you will find many sample files, PBIX files that you can download, and include the most
sophisticated elements in. So all the Power Query
preparations and all the oldest sophisticated
measures in duck's are available as PBIX files
from my website, from my blog. >> Excellent. I did put
the link to your blog there and I also put the
link to the AppSource app. So have a look in the chat
window and you will see those. We have one more
question from Powell. He said that when he connects, sorry. When he connects to the dataset, is it possible to add more tables? For example, age structure
by country, etc? >> That's a great question. In the current implementation, you cannot include additional
tables into the solution. Just because in any data set as you here when we
built our only port, we are using the live
connection to the data set and this functionality doesn't support a combination of
multiple reports together. For you in this case, you would need to follow my blog, download the PBIX files, and start adding to them
the competent you need. You're also welcome to contact
me and if you have an idea that makes sense to the community I will consider to build it
as the next version. >> Awesome, let's hope that
answered your question and Salvador said that he
will do that. Thanks so much. >> Great. Cool. Let's move to the next
part of our session. I want to show you and even
if you forget my blog, forget anything, just install, find my app and install it. You will find out. Just to give you an
example in other pages, you can find my blog
if you didn't follow my bad thing to remove my logo. You can find a link to my blog with the specific filter on my blog for all the
articles that are related. Just to show you here. You see this
is the Datachant with a tag of COVID-19 and you start from the bottom and move
upwards step-by-step. You have many, many
different tutorials. Of course, the PBIX file, to go through a sequence of technical challenges that will allow you to build your
own COVID-19 import. The tutorials are split
into two categories. The first one is parallel query. This is the data transformation
technology inside Power BI and other
Microsoft products, allowing you to combine data for multiple sources,
bring them altogether. For example, in my case, when I build this solution, you can see that I have here, I bought also US population data and I have also world
population data, so I combine population
and also density. As you can see here, I have also density and all these combined into some of
the calculations that I have. This is the Power Query
technology and you have some of these tutorials available
for you to follow. The other type of
walk-throughs that we have, tutorials that we have is for tags and modeling and
time intelligence. You will be able to appreciate
both kind of technologies, very powerful technologies inside Power BI that he would want to test. Now, if we have you today as part of anyone who is listening
to this webinar later on, people that understand a
bit about Power BI and think that Power BI
is just that nice, cool visualization tool, this is why I'm really happy
to be here today, explaining and sharing with you the great news that Power BI
is a full-stack BI solution, allowing you as a
practitioner of Power BI, to do things probably 10 times faster than any ETL developer that would use enterprise ETL technologies or modeling technologies
outside of Power BI. The ability to grab data, combine it, prepare it, and build very sophisticated intelligence on
top of it within the same tool. Really a game changer
and if you are new to this technology and you don't want just to focus on cool visualization, this sequence of tutorials
can really help you to start your journey and
moving to the next level. What we are going to
do today is spend more time on the data
populations element of how to build your
own COVID-19 dashboard. Our starting point is the
John Hopkins data set. Okay, so you see I have it here. Let's open a new Power BI file, so I'm opening Power BI
Desktop from blank to show you step-by-step how we're
going to build this solution. What I'm going to do now, I'm going, as part of the John
Hopkins data repository, I'm going to click here on
the COVID-19 data folder. Here I'm going to take the CSSE COVID-19 time series
and under the time series, I'm going to take the confirmed, let me just zoom in to show to you. We are going to take this
specific data set, okay? I'm clicking here and now in
my browser you are going to see that I have this specific
file with all the data. If I may go under the low section here and click
here on the row section, you can actually see this is the CSV file with all the
data of confirmed cases. Our starting point would be to go to our browser and copy this URL. Now I'm opening the
new Power BI Desktop, clicking in "Get Data" and inside get data I'm going to
select "Search here for web". If this is the first time that
you bring data in Power BI, you can see massive amount
of data connectors, allowing you to connect to
any data repository on earth, probably if you just
have the permissions. I'm selecting in web to
search for the web connector. You see this is my web connector
and we click on "web connector". Now I'm going to paste the
URL from John Hopkins, so you see this is the CSV
file and I will click "okay". I will click here under the "transfer data" and now this is
the Power Query Editor. Let's click on "Refresh"
because I did it in the past. This is the Power Query Editor, allowing you to bring multiple
data sources together, mash up the data, prepare the data, clean it up, explore it also. Even if you have very
clean enterprise data from your data warehouse. This is an amazing tool that allows
you to discover new anomalies, new outliers in your data as well. You can use this tool
to do that as well. In our case, we have this data
for the purposes of the demo, let's just say that we don't really care about
latitude and longitude. I can just select these two
columns and press "Delete". You see that we have here the
province, state, country, region and then when I
scroll to the right, every column represents
a specific date. When John Hopkins University tomorrow will update this
data source on GitHub, they will actually add it on the
right another column for today. Actually, this case is interesting. Let do a refresh just to confirm. Right now, last time
that they updated this data source is August 17. We are hoping that they will
give us more data soon, okay? But this is the data that they
have and we need to bring it in a format that will allow us to
build the report in Power BI. Now this format is really bad, doesn't allow you to do almost anything and we
need to flat it down. The first step for us would be to
promote the first row as adults. I'm clicking here under
"useful stores, adults". Now you can see that we promoted the first row
and the second thing, and this is one of my favorite
functionalities in Power Query, what we are going to do now is
select the first two columns. Till now the solution that
actually boiled the data, except of these two columns, everything on the right is a date and a confirmed case and we
don't want this format. We want a table with
province and state and the country and the date and the total number of
confirmed case as are reflected in the values
of all these columns. After I selected these two columns, I can go and right-click
and here I can just go to "Unpivot Other Columns"
and now see what would happen. After I select "Unpivot
Other Columns", I got exactly what I need. I have the date, I have an value, the country and province state. In some cases like in Afghanistan
or many other countries, John Hopkins University data
set doesn't provide you the next level to drill down
for specific province or state. But for other countries, let's just explore it to show you. If I will, just for a second
filter all the blank values, you will be able to see that
we have here for Australia, Canada, China, Denmark, France, Netherlands and the United Kingdom. We do have those drill downs. Now by the way, you may say, "Gil, how did you bring information
from US and states. We don't see in United
States state or county data in this
report?" And this is right. There is another file
that you can use from John Hopkins to bring you this
data and combine them together. Now let's just remove
these two little steps. Again, let's do it again. We selected these two columns and right-click and selected
"Unpivot Other Columns". Now we have attribute and value in the right way for
us to keep working on it. Now, you may see here I
can change attribute to date and change value
into the confirmed cases. Now, when I did this
operation under the WHO, if you are familiar with Power Query, this will not be new to you. By default, when you start
your journey with Power Query, you will find out that you
don't have any Formula Bar. But I really recommend for any user, please if it's the first time that you work with Power Query editor, please go to the View Tab and make sure that the Formula Bar is enabled, and you will see why in a minute. Basically once the
formula bar is enabled, you can see that you have
a strange language here, strange if you're not
familiar with it. It was strange for
me definitely when I just learned about it
for the first time. This formula bar is part of
the M language expression, M like monkey or mashup, which allows you to
do transformations, and data wrangling inside
Power BI and Power Query, with capabilities that are beyond what you have available
in the user interface. For example, here, remember when we changed the column from
attribute to Date, this specific code was
generated by Power Query. As you will do more and more
advanced data preparations inside Power BI, you will discover that
understanding even a bit, I'm not even telling
you learn the syntax, but if you just can read
table and name column, you understand, oh, okay, this step is renaming some columns. Then you can see that you have
some double quoted texts, that you can probably understand in many cases these will
be either column names, like in our case, we
renamed attribute to Date and we renamed value
to Confirmed Cases. So start to see what you
have in on the formula bar, to learn more about it, because it will turn out to be a
very useful tool in your arsenal. If you are more advanced, and you're into
programming languages, and you have capabilities
to write scripts, in all different technologies, and it is the first time that
you see this technology, I really recommend also that
you will go around their home. Click the "Advanced Editor", and this is where you can
see the full M expression. Let me just one second. So this is the full M expression. Each line in the code here
was generated by the UI. But if you know the syntax, you can actually build a very sophisticated
[inaudible] transformations, and you can use the UI to speed
up your development time. You use the UI, and then you
make some lightweight changes, that will allow you to take the
logic that you've built deal, into the next level. Now sometime in new users
of Power Query being asked, they received a challenge
in Power BI form sign one with a very challenging dataset, and they are not sure, if parallel query can support the
preparations that the challenge to clean the data or transform it in the right
format that they need. An easy way for you to understand whether parallel query
is the right tool, or even if it's not always the
best tool to get what you need. Whenever the data needs a
transformations and clean up, and you have a deterministic way
to describe what you need to do, no matter how complex the
transformation that is required, you should know Power
Query can support it. If it's not fully deterministic. This is where Power Query
may supported it or may not. But for any complex transformation, any complex logic
that you need to do, you just need to know it's
possible with Power Query, and the combinations of the UI, with lightweight touches
of this M expression, can get you to do almost
everything, and most likely, 10 times, 20 times faster than any other ETL work
that you would find. No matter which technology, no matter which language, you bring your top expert Python
Developers in the same room, with top experts in M, and you'll probably find
out that Power Query can achieve the same
objectives 10times faster. Let's move back to our scenario. We have this Power Query
here with the Province, Country Region, Date,
and Confirmed Cases. Let's rename this table, let's call it Confirmed Cases. I wanted to show you
another reason why it's a good practice to take
a peek in the Formula Bar. If you will go to the first you see on the right side we
have the Applied Steps, and each one of these steps, was the step that we have done
through the user interface. It's like sometimes new users are thinking this is
like a macro recording. Each one of these steps we
have done through the UI, so it's being recorded. But the entire pipeline
of transformation will be generated next
time we'll do a refresh. What I wanted to share with you, what would happen tomorrow, when John Hopkins University will add new columns, into the database. These columns will be added
ill on the right side known. It is sometimes really important that you will
go through the steps to make sure that tomorrow
when you add the refresh, the data that is going to change
will not break up your solution. In our case. Let me just show you here. You see in the Formula Bar, if I will just expand it a bit, you can see that we have something
relatively very, very strange. Can you see that? Let me just show it to you. Here we have a specific element. This specific section here, was generated by the user interface, that we didn't type this code. But you can see here an explicit
mentioned for 213 columns. So tomorrow when John Hopkins
University will publish a new data, and the new column will be added. You can see also here on the right, this is column number 213. When column 214 will
be added to the data. You will do a schedule
refresh for this solution. This element will not work for
you because in Power Query only loads the first 213 columns. So for that case, if we're just deleting this
section from the code, now everything works the same way, and we go back to our solution
here,just one second. Now when we reload this solution, and set it to schedule
refresh on the service, everything will work
for us as we expect. Now I know we have only 10 more
minutes for the session now, and I want to make sure, that we have also some
time for answers. Before we go through an open Q and A, I just wanted to show you. So here you see we
loaded on the data. We probably forgot, for example, to change the Dates, and the Confirmed Cases, so we can go always back. After we finish this refresh, we can go back to Home. Let's open the ribbon, you'll see under Home, I can click "Transform Data" to
go back to Power Query, and here, I can turn the Date into a Date, and the confirmed Case
into a Whole Number. Now when I load again the report, you will see an icon
for Confirm Cases. I can click on "Confirm Cases", and let's get also the Date
and change it into a line, and you can see now, you have the Cumulative Numbers. Cumulative Confirmed Cases. We let it bring for example, let's change it into a
map, where is the map? Bring the Country. So you see the Date should be, let's remove the day for now. We can see now how the COVID-19 is being distributed
across different countries. It just took us the count of like, five minutes without my
explanations to get to this level of of work. You published this report, and it will keep refreshing to you. This is a relatively basic, few UI clicks and manual fix to
accommodate for new columns, allow you now to build
your own dataset, and own the transformations, and to build the tend to work
Solution for your enterprise. By that, let me just very quickly, share with you a sneak peak for what you are going to do
in the next session. In the next session we will go over another very
interesting scenario, that I published on a blog recently, where we will go into the health organization. World Health Organization website. You can see we have many
different PDF files. Each one of these PDF files, just to give you an example,
inside somewhere, table. You see this table with the same data that we
have in Johns Hopkins. So what we are going
to do is showing you how to use Power BI Power Query from data preparation, data cleanup, technology and experience to bring not just a single PDF file
and bring the specific table into your data set but to
do it across multiple PDFs. Actually, we are going to load all the PDF files from this website. We're going to do that just without any other technology
but Power BI Power Query. So that's what we're
going to do next. Now, I know you may say, "Why do you need to do
that if you already have Johns Hopkins data set?" Make sense. But I'm sure that in
your organization, you have some scenarios
where you store data in PDF files in a folder
on a repository, and you have some very important
data that you don't own, you got it from vendors, from third party companies,
from another team. An IT doesn't have any resources to bring it into
the data in your organization. Now, with Power BI, based on the techniques that you
will learn in the next session, you will be able to import the data and combine it together
into a Power BI solution. By that, for the last five minutes, I will be glad to answer any
questions that you may have. >> Thank you, Gil. That was exciting, and we're looking forward to
the next session as well. We have a couple of questions here. One of them is, sorry, it would be great to have a
percentage of positive test rates. >> Yeah. So that's a great comment. I think with Johns Hopkins, I didn't find an official
data source for testing. But definitely if you have
a good resource for testing across different
countries and regions, we'll be glad to include
it in the next version. But definitely you can combine it with Power Query into any COVID-19
data source that you may. >> Okay, awesome. Then someone asked if we can share the
link to the data set here. I think you have the instructions to get all of that or is there something different that we need to add? I think we provided the links. If you come to the chat window above, I can repost the links if you like, and I'll do so now. >> Okay. So let me just very quickly, for my tutorials or
for the AppSource? >> It doesn't specify. So we'll let the person respond
if he could, that would be great. Then Salvador said he had
a similar report with the same source but still not refreshed since
a couple of days ago, where could the problem be? >> If you're using Johns
Hopkins data source, we just saw that the last
data came from August 17. So for the last two, three days, we don't
have anymore data. That may be the reason. But you are welcome to
contact me on Twitter or on my blog as a comment and if
you can send me the file, I can try to look at
it and give you a tip. >> Great. Then Pauson. How do we set up automatic
refresh of the report? Do you need a gateway? Do you need a pro-license? >> Yeah. Great question. So
you would not need anything. You don't need the gateway. The data source is already
Johns Hopkins University and even the WHO data sources that we have also doesn't need a gateway because the data
sources on the Cloud. So schedule refresh
will just work for you. >> Okay. Then somebody asked, how do we find out when you'll
have live presentations? >> How I have live presentations? So the question is, why don't you don't you do
more live presentations? You can try to follow me on Twitter, and if you need any live presentation and
you bring enough audience, we can do a live presentation
whenever we have the availability. So you are welcome to contact me. If you bring enough audience, I will be glad to do things live. >> Awesome. Then we had a
couple of questions regarding, can you please suggest some resources to learn DAX from a beginner? I'm struggling to
create formulas that required date and time calculations. Oh gosh, there's a few
sources out there, Gil. I mean, you can provide some
and then I can provide some. There's a ton MVPs. >> You're welcome to provide one. Yeah. We will have the same. >> Yeah. Okay. So
basically Matt Allington, also Phil Seamark from Microsoft, and also date and time calculations. Reza Rad, who has a
time-intelligence. He just did a
time-intelligence webinar. So I can just grab those links now. We still have another minute
and see if I can grab those. But if not, just let's see. Then we also have another question. I've been following Gil's data champ blogs since the
start of the year. Simply thrilled to be listening to
him on this webinar. Thank you. The learning moment for
me in this webinar was the installation of an App Template looking forward to the next one. Then our last one is problem
found with the M code. Thank you. Awesome. That's fantastic. So let's get those links. Any other questions while
I'm looking for those? We do have another question here. No, that's all good too.
