Practical3 PowerBI1
Practical3 PowerBI1
Practical3 PowerBI1
On the Power BI Desktop Home tab, select Get Data > Web to connect to a web
data source.
Page 1 of 10
If prompted, on the Access Web Content screen, select Connect to use anonymous
access.
At this point you can select Load to load the table, or Transform data to make
changes in the table before you load it.
Page 2 of 10
Shape data
Now that you're connected to a data source, you can adjust the data to meet your
needs. To shape data, you provide Power Query Editor with step-by-step instructions
for adjusting the data while loading and presenting it. Shaping doesn't affect the
original data source, only this particular view of the data.
Shaping can mean transforming the data, such as renaming columns or tables,
removing rows or columns, or changing data types. Power Query Editor captures
these steps sequentially under Applied Steps in the Query Settings pane. Each time
this query connects to the data source, those steps are carried out, so the data is
always shaped the way you specify.
Notice that the Applied Steps in Query Settings already contain a few steps. You
can select each step to see its effect in the Power Query Editor. First, you specified a
web source, and then you previewed the table in the Navigator window. In the third
Page 3 of 10
step, Changed type, Power BI recognized whole number data when importing it, and
automatically changed the original web Text data type to Whole numbers.
When you select Transform data, Power Query Editor launches, with a representative
view of the table. The Query Settings pane is on the right, or you can always show it
by selecting Query Settings on the View tab of Power Query Editor.
We need the values of first row to be the headers. Click on Transform > Use First Row as Headers.
Page 4 of 10
Now, the first row does not make sense; Let’s remove it. Click on Home > remove rows > Remove tops
rows.
Since we are removing the first row, we insert 1 in the below window.
Page 5 of 10
All those scores are text rather than numbers, and we need them to be numbers. No
problem. Just right-click the column header, and select Change Type > Whole
Number to change them. To choose more than one column, hold down the Shift
key to select several adjacent columns, or Ctrl to select non-adjacent columns.
Either right-click a column header, select Change Type, and choose a new data
type from the menu, or drop down the list next to Data Type in the Transform
group of the Home tab, and select a new data type.
You can now apply your own changes and transformations to the data and see them
in Applied Steps.
For example, for sunglasses sales you're most interested in the weather ranking, so
you decide to sort the table by the Weather column instead of by Overall rank.
Drop down the arrow next to the Weather header, and select Sort ascending. The
data now appears sorted by weather ranking, and the step Sorted Rows appears in
Applied Steps.
Page 6 of 10
You're not very interested in selling sunglasses to the worst weather states, so you
decide to remove them from the table. From the Reduce Rows group of the
Home tab, select Remove Rows > Remove Bottom Rows. In the Remove
Bottom Rows dialog box, enter 10, and then select OK.
The bottom 10 worst weather rows are removed from the table, and the step
Removed Bottom Rows appears in Applied Steps.
You decide the table has too much extra information for your needs, and to remove
the Affordability, Crime, Culture, and Wellness columns. Select the header of each
column that you want to remove. Hold down the Shift key to select several adjacent
columns, or Ctrl to select non-adjacent columns.
Then, from the Manage Columns group of the Home tab, select Remove Columns.
You can also right-click one of the selected column headers and select Remove
Columns from the menu. The selected columns are removed, and the step Removed
Columns appears in Applied Steps.
Page 7 of 10
On second thought, Affordability might be relevant to sunglasses sales after all.
You'd like to get that column back. You can easily undo the last step in the Applied
Steps pane by selecting the X delete icon next to the step. Now redo the step,
selecting only the columns you want to delete. For more flexibility, you could delete
each column as a separate step.
You can right-click any step in the Applied Steps pane and choose to delete it,
rename it, move it up or down in the sequence, or add or delete steps after it. For
intermediate steps, Power BI Desktop will warn you if the change could affect later
steps and break your query.
Page 8 of 10
For example, if you no longer wanted to sort the table by Weather, you might try to
delete the Sorted Rows step. Power BI Desktop warns you that deleting this step
could cause your query to break. You removed the bottom 10 rows after you sorted
by weather, so if you remove the sort, different rows will be removed. You also get a
warning if you select the Sorted Rows step and try to add a new intermediate step at
that point. (Hopefully you don’t do that!)
Page 9 of 10
Finally, you change the table title to be about sunglass sales instead of retirement.
Under Properties in the Query Settings pane, replace the old title with Best states
for sunglass sales.
The finished query for your shaped data looks like this:
Additional changes to the table can still be made after it's loaded, but for now this
will do. When you're done, select Close & Apply from the Home ribbon, and Power
BI Desktop applies the changes and closes Power Query Editor.
When you finish, select File > Save and save your file with your name and upload it
to LMS in the Practical 1 Power BI section.
Page 10 of 10