Building a QC Dashboard Pt. 3

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

I know I am!

In Part 2 we used the Power Query Editor to delete some rows and columns to our query. In this article we will add a few more steps that focus on transforming the data.

Let's get started!

Part 3 - Transforming Data

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

My Building Details data has a column called Building Type. The data looks to be two different kinds of information separated by a "-". I would really like to create two new fields with the data from the left of the hyphen in one field and the data to the right of the hyphen in another.

If you are familiar with Excel, this can be done using the Text to columns function. Power BI has a similar function called Split Column.

With Building Type selected click Split Columns. Notice there are more options available then Excels Text to columns. Here, I will be using the By Delimiter option.

Once selected, a pop-up window opens with a number of options. For my data, the delimiter will be the hyphen. Since hyphen is not one of the options, I will select --Custom--.

One of the coolest things about the custom option is, unlike Excel, we are not limited to one character. This means, if my delimiter was a string of characters, like " - ", a space hyphen space, I could use that as a delimiter. Yeah, very cool. :)

However, we are just using the hyphen.

My next option is the Split at. This gives us control as to where to split the data by. If there were multiple hyphens in my Building Type field, but I only wanted to split it by the Left-most delimiter and ignore the other hyphens, I would select that options. The same goes for the Right-most delimiter. The Each occurrence of the delimiter is just that. Every time Power Query finds a delimiter (a hyphen in our example), it would create a new field or column. So, if you had a phone number field with 1-203-123-4567, and used that option, you would have four new columns in your data set.

I normally do not need to use the Advanced options setting but you have the ability to spilt the data into Columns or Rows and be able to split by a given number of columns. I also do not use the Quote Character or Split using special characters option but it is available if you need it.

I am good with the below settings; so, I click OK.

And now we have two new columns, Building Type.1 and Building Type.2. We can also see that two new steps were added, Split Columns by Delimiter and Change Type 1.

I can now change the name of the column headers of the two new columns, so that they make more sense.

I can double-click the column header to give it a better name. I will call the first one, Building Category and the second one Building Sub-Category. After doing so, another step, Renamed Columns is added to the list.

There is one more modification I would like to do and that is to concatenate, or join, the Address, City, State, and Zip code columns into one column and give it a name of Full Address.

To do this, I navigate to the Add Column tab and select the Custom Column function.

A pop-up window will open. Here we can give our Custom Column a name, using the New column name field. Then we can build our formula.

To do that, I found and double-clicked the columns I wanted to add from the Available columns section. I also added the "&" character between the columns. This is just like saying I am combining this column AND this column AND this column.

Notice that I also have some stuff inside double quotes. If I just combined Address and City, there would be no space and the result would look like 1 Hacker WayMenlo Park.

Not the result I am going for.

So, I separate Address and City with a comma and a space. The same between City and State. Then I separate State and Zip with just a space.

Below is the result.

Looking good to me!

Our data is starting to shape up! In the next couple of articles we will look at the Floor and Space Details data sets and see if we need to make any transformation 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. :)