EXCEL PIVOT CHART QUERY HOW TO
EXCEL PIVOT CHART QUERY MANUAL

We can finally same this Power Query connection, but we will use the вЂњClose & Load To вЂ¦вЂќ option and just create a connection. The Power Query documentation is of no help neither. Why Power BI is based on locales (what they call cultures) and not on patterns is a mystery to me. For some people (like the French), you need to add an extra parameter to the Table.TransformColumnTypes which is вЂњen-USвЂќ. For change the date type, itвЂ™s easy if you are in an english locale. The rest is just renaming some columns and changing types. That will remove all the other columns and provide only two new columns, Power Query makes it very for us, you just have to select the columns that should remain columns and select вЂњUnpivot Other ColumnsвЂќ. It is always easier to manipulate data where the number of columns is small and fixed. As a rule of thumb, you should never have dates as column. Now comes the time to put the dates as rows instead of columns.

EXCEL PIVOT CHART QUERY MANUAL

Then, in the вЂњSourceвЂќ step, you need to manual edit the formula and remove the вЂњColumns=43,вЂќ part (the number will be bigger every day). The first thing to do is to delete the вЂњChange TypeвЂќ steps (we will address that later). Remember, every day there is a new column. Nevertheless, as you can see in the formula section, Power Query try to be smart and to convert some columns as numbers. The easy thing to do is to set the first row as column names (highlighted button in the capture below). Cleaning the data with Power QueryД¬licking on Transform Data will open a Power Query window. There is a bit of cleanup to do so we will transform the data first. It is almost that simple and we immediately get a preview. Under the Data panel, we click From Web and put the raw file url. We will use Excel for the data transformation and modeling, but Power BI Desktop would be quite similar.

Each date, this file gets a new column and sometimes new rows. For each file, the first four column provide the location and we have a column per date. There are three files, one for the number of confirmed cases, the number of deaths and the number of recovery. We will use the csse_covid_19_data/csse_covid_19_time_series. Luckily for us, the Johns Hopkins University provides a wonderful Github with updated data. вЂ“ Accessing a single data point from the Data Model with Excel formulas вЂ“ Import data from Wikipedia in Power Query вЂ“ Define a custom function in Power Query as standalone or inside another query вЂ“ Using the culture parameter in Power Query

EXCEL PIVOT CHART QUERY HOW TO

вЂ“ How to unpivot with a variable number of columns вЂ“ How to import a CSV with a variable number of columns вЂ“ Define smart metrics using with Power PivotДЄlong the way we will learn the following things :

As it provides a very good real life example of using Excel, Power Query and Power Pivot tools, this article will guide you through the process (and some bottlenecks). Luckily for me, IвЂ™m love swimming in data so what better usage of a rainy Sunday afternoon? After some hours, IвЂ™ve got my very own dashboard (see below). Dashboard from Dong, Du and Gardner (screen caputure) It displays all the numbers but doesnвЂ™t provide a clear information of the development of the situation. There is a nice dashboard from the Johns Hopkins University but it didnвЂ™t fit my need. As everyone, I want to monitor the propagation of the Covid-19.

Excel pivot chart query