Mastering Data Processing in Power Query
Translated by
ScribdTranslationsMastering Data Processing in Power Query
Translated by
ScribdTranslationsData processing with Power Query
Learning Power Query is becoming a necessity to go further with Excel.
This component made its appearance with the 2013 version of Excel and since 2016.
is natively integrated into Excel. It is gradually replacing some
historical features of Excel.
What is Power Query?
Power Query allows you to perform bulk operations on your data.
You will be able to integrate multiple data sources and manipulate them.
wish.
Power Query offers the ability to handle data like a chef.
manage your ingredients. You will be able to cut, separate, combine, add
additional data and cook your data to prepare your reports.
It looks like Excel in a way, and at the same time, it is very
different. Truly different.
In addition to performing these operations in bulk, you will be able to create a model of
data from a simple table. In order for this to make some sense, you
you must have large tables (in number of rows and in number of)
columns). Thus, you will be able to really start extracting the nectar that
represents Power Query and you will soon want to learn it!
What types of operations can be performed with Power
Query ?
From now on, you need to think of your data as a
Together. The operations apply only to the whole.
Thus, the 'Add' operation does not correspond to any addition. If you think
"data set", "Add" will mean "Put more data in
my set". In the article,how to merge multiple Excel files, I do it
précise déjà dansle scénario d’utilisation de Power Query.
The main operations of Power Query
• Obtain data - Integrate data from Excel or elsewhere
• Add data - Append to the datasets of
same structure (For connoisseurs: UNION in SQL)
• Merging data: Enriching the data of a set with the
data from another set (For those familiar with JOIN in SQL)
• Add / Remove columns - Different methods of adding
existent (calculated column, automatic row index column by
example)
• Delete lines according to your criteria (removal of duplicates,
suppression of all lines except duplicates for example)
• Transpose your data - An operation that exists in the form
offunction in Excel 365from now on
• Pivot one or more columns - This operation should not
a lot to talk to you at this stage (and nothing to do with the Pivot Table (either the
Pivot Table.
People who read this article also read: Use a CSV file with Excel
De nouveau, à ce stade, vous vous demandez encore quel est l’intérêt
to learn Power Query? Excel has done all this... It's partly true
only (I challenge you to rotate several columns without
programmation…).
But, because there is a but, Excel does not know how to automate (without programming)
the execution of these operations! When is this necessary? When you
receive files from your suppliers, colleagues, or clients to analyze.
Let's illustrate these transformations using a file provided by Johns University.
Hopkins on COVID-19. But just before, one more word about the place that takes up.
Power Query within Excel.
The retrieval of external data is already based on Power Query.
When you use the features found in the Data tab >
To obtain data, Excel uses Power Query.
Step n°1 bis: Import your CSV file directly from its web source
This option is interesting for having updated data
daily from your Excel file. Indeed, the file address does not
changes from one day to the next. This makes its web import very simple and very
effective.
A small warning nonetheless: insofar as Excel will perform some
web requests, you will receive a security warning each time you open it
file. For you who created the file and defined the data source, no
problems. However, if you send the file to another person, that person
could (should!) rightfully be wary.
Go to the 'Data' tab and then 'From the web' and paste the address
complete the CSV file
: [Link]
19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid1
9_confirmed_global.csv
Step no. 1 (ter): Connect to the web content
An additional step for the web content connection option
directly, the authentication. In this case, nothing to configure, access is
free and anonymous (in the sense of authentication).
Ici, vous n’avez qu’à vérifier que c’est bien «Anonyme » qui est sélectionné à
left and then click on 'Log in'.
Step no. 2: Transform the data in Power Query
This time you are almost there! After this step, you will find yourself in
the Power Query interface and will be able to start learning how to use it!
Here, you will click on 'Transform Data' (this applies in the
two scenarios: import from a CSV file or from the web) :
The interface below presents the different integration parameters of
web data; in particular the encoding of the file which is in utf8 format which is
used if you don't have an accent; the comma is used to separate the columns;
so we can load this data
When the data is loaded, it goes directly to Excel and we have the tab.
worksheet queries appear on the right.
The data is raw because the column names 1 2 3 are not very
pleasant to read.
ACCESS TO POWER QUERY
RIGHT CLICK + MODIFY
To start data processing, we go to queries.
The requests correspond to the data you have collected.
exposed to Excel
Rename the request
A request corresponds to the data that you will collect; retrieve and that
you will then expose to excel
1 used the name of the first row as the column name
Different operations appear
By clicking on source
Power Query has changed the data type, for example dates have become
integers
The column names are correctly named, there is one column per
day for each of the statistics related to covid
Pivot the columns in Power Query
Right-click on raw data and then modify
How to keep these first 4 columns and transform next to them
everything online AND HAVE TWO COLUMNS 1 for the date and 1 for the statement
To do this, select the first column and click on shift+end.
Result
Press ctrl + down arrow to get the number of records
Transform the date data into date format and remove the sources
For this, we return to raw and modified data.
Then we carry out a grouping.
If you close the power query, the number of rows decreases in Excel.
Result on Excel after a few minutes of updates
Finally, transformation of date from Anglo-Saxon format to Western format