Original subtitles

So we've already seen some options for formatting or changing data types within the query editor.

We formatted columns as dates as whole numbers as currency and decimals but there are some additional

options that we'll find outside of the query editor in the data view.

Specifically the modeling tab that will help us not only define more specific data formats like specific

date or currency types but also categories and many of these categories that you see here are geographical

in nature they capture things like addresses cities latitude and longitude etc..

The purpose here is that categorizing your data in this way especially geospatial data helps power by

accurately map and visualize those location based fields.

Those things like addresses countries continents zip codes etc..

So let's head back to our adventure Works report and I'm going to show you a couple examples of how

we can do this.

All right.

This is one that you're gonna need to follow along closely with because we're gonna be modifying some

of our data formats and adding a new table or a new connection to our file.

So first and foremost let's head to our data view which we haven't really spent much time here yet.

And within this view you can see previews of your tables here my four tables and the right side of the

screen can drill into different individual fields as well.

Now looking at the calendar table you'll note that the dates have defaulted to this long form format

and there's nothing wrong with that.

It's still correct.

I would just prefer a shorter more concise format.

So when I select the column these formatting options within my modeling tab now become available.

So the data type is set to a date which was established within the query editor but now I have more

specific formatting options.

So under the date time menu actually like this first option month day year is this kind of the classic

standard English US format.

So I'll just click that and it will refresh and basically overwrite that long form and format it in

that new kind of shorter form date format.

Go ahead and do the exact same thing to my start of week column as well start of month and start of

the year

so that just kind of clean things up a little bit.

I also have a date field in my sales table.

In fact I have to date fields the order date and the stock date.

So let's go ahead and just select each one of these and format in that same short form.

Date and now one other adjustment with the data that we already have.

We jump into our product lookup table and scroll to the right.

Remember we hadn't really been able to format those currencies exactly how we wanted to in the query

editor.

So same idea here we've got the data type decimal number we can actually format as a specific type of

currency.

So many more options here than we had within the query editor itself.

So when I choose the dollar sign in English format and there you go repeat the process

for product price and then for discount price as well.

There you go.

Now these are nice and clean properly formatted U.S. currency fields.

Now last item I want to address here is this data category field and this is what I showed you in that

slide with those geospatial fields you've got other kind of categorizations like web your l even barcodes

in image urls so to show you how that works actually want to connect to a new table so I can go to Home

get data text CSP and this time the field that I want the table that I want is adventure works territories

double click.

Fire up the preview pane default looks good edit it in the query editor and as you can see pretty simple

table here.

Only about 10 rows we've got sales territory keys and each key is associated with a given region country

and continent.

So this is gonna help us understand for those sales in our AWP sales data where are they coming from.

What countries what regions which continents are driving the most sales.

Help us kind of answer questions like that.

So first things first.

You know the drill.

Let's change this table name to a W underscore territories and you guessed it.

This is a look up table.

So a W territories look up press enter.

We've got proper headers.

Our key is a whole number everything else is text to that looks good.

Go ahead and close and apply and now as you can see we've got this new table that's popped up here at

the bottom of our field list and the data view.

And just to save space minute collapses other tables and drill into that territory look up and there

you go.

That looks nice and familiar.

So last step here we will want to visualize some of this data using the map tools and visualizations

which are really really handy in the report view.

So to make sure that power by visualizing things properly let's go ahead and use those categorisation

options in the modeling tab.

So we'll start with country select the column and under the Data category dropdown carefully you don't

click County because that's different from country.

I've made that mistake before.

You want country slash region and then for continent you've got a continent option as well.

And because these regions are kind of custom defined North West Germany UK they don't really follow

a standardised mapping syntax.

We're OK with just categorizing country and continent for now.

An important note here.

Power B I will almost always recognize these fields properly by default automatically.

So had we not gone through this step and just plug these countries into a map visual ninety nine point

nine percent of the time it's going to work exactly as you'd expect and you'd get accurate results.

But as a best practice it really couldn't hurt to explicitly define what types of data categories you're

working with.

And last thing to note here these little icons to the left of the fields see a bunch of different types

and we'll talk about what these mean but the two little globe icons indicate that these have been categorized

as geographical fields.

So a nice little helpful visual cue there.

So let's return to our relationships.

You see that territories table object pop up.

Now you've got five connections here in our report.

Let's go ahead and give it a save

and keep cruising ahead.