Building a QC Dashboard Pt. 4

Hello everyone! Are you ready for?Part 4?of our QC Dashboard journey?

I know I am!

In?Part 3?we used the Power Query Editor to?modify and transform some of our Building details data. In this article we will look at the Floor and Space details data and apply the steps we have learned.

Let's get started!

Part 4 - Transforming Data continued

Below I have the Power Query Editor open.?*As a refresher, I got there by clicking?Transform data?on the Home tab.

We have moved on from the Building details data and are now looking into the Floor details. The first thing I notice is that the data appears to start on Row 3 of the preview. This is the same thing we ran into with the Building details data. So, I am will be removing those rows and promoting the correct row to be my column headers. *As a refresher, we did this in Part 2.

I also have a few columns that I need to delete. Again, same as like we did for the Building details data. I will go ahead and removed those.

I am also going to change the format of the Floor Level column to be Text instead of Whole Number. We won't need to do any calculations of Floor Levels, like adding floor 1 to floor 9. So, formatting as text just makes sense.

As for the rest of the Floor details data, everything looks pretty good as-is. However, I will take this opportunity to show another data modification function called Transform. Let's say I had data that I wanted to Capitalize the first letter in each word, like first and last name. I could do that with this function. Below are the various options that are available. I am going to take the Region field an make all the text Uppercased.

First, I right-click on the Region column header, then go to Transform and select UPPERCASE.

Just like that, a new step is added and each character is now uppercase.

So, why would doing this be useful? Good question!

Let's say your data is merged from different places. For example, different departments all need to submit a the same report; so, different people are filling in the data. Some people may use all caps (JASON WEST), others may capitalize the first word (Jason West), others may enter their data in lowercase (jason west). To a computer, these can be looked at as three different things, when in reality it may all be the same thing. Instead of cleaning the source data, you can just modify it with Power Query. If I change the field to put everything all to uppercase, now the program will treat every instance of "Jason West" as on thing. This could help when trying to match addresses or people names, as in the above example.

Now on to the Space details data!

Here we had the same issues as the two previous sources. I have removed rows, promoted headers, deleted and formatted a few columns. As with the Floor details, everything else looks good-to-go!

Now that we are done with modifying the data, we can close the Power Query Editor and load our changes into Power BI. Of course, we can always come back to the Editor and add modifications if we need to.

To save and load the changes, navigate to the Home tab and click the Close & Apply button. Then select Close & Apply.

The data is now loaded!

But wait, I don't see anything...

You can see what was loaded by clicking the little table icon the red arrow is pointing to in the above image.

Clicking the table icon brings you to the Data view. The red arrow below shows where the data sources we loaded can be clicked and explored.

In Part 5 we will go over the Model view, where we will link the datasets together.

We are getting close to the fun part of building the QC checks! Hang in there!

Thanks for reading and if you have any questions,?do not hesitate?in adding a comment or messaging me. I am always willing to help! And if you liked what you read, share the love by sharing the post. :)