Power BI - Transferring less structured data

Hello friends and welcome to Tutorials Point

In this segment of the video we are going to see the transformation of less

structure data in Power BI desktop. Now so far you must have got the idea that Power BI desktop is all about order

and organized data structure. What do I mean by that? Whenever I import any kind of data, I always make sure that the column should have the

proper data types. So let's take for an example, if it's a date, I should always follow the date

data types. If it's a text, it should follow text and if it is a time it should follow

time data types. That's right. So this is what the organized data structure is all about

Now whenever I want to make any kind of changes or whenever I want to change the data types

of the table that we have imported, we always need to go to the Power Query Editor

In Power Query Editor, I can make the transformation the way I wanted. In that, I can reorder the

column, I can delete the column, I can give the data types and I can get a

organize data all together in the power query editor. Now in this power query editor

whenever we apply any kind of changes these changes can be seen on the

extreme right side of your screen under the applied step section. Under that

applied section you can see the each and every move that you make and it's

been recorded every time. Now let's take for an example that sometimes you

tend to do some mistake. You must have applied some wrong data sets. So what we

can do about it do a control Z no we do not have an option for undo that is

control Z in power via instead of that what we can do we can go to that applied

step section and the step that you have applied you simply have to hover your

mouse and at the end of it you will find a cross sign over it you simply have to

click on that cross sign and that changes can be undowed like that so how to

apply this transformation and how to get our organized data will be having a

better understanding of this in our practical approach hello friends and welcome in

this video we are going to learn how to transform a less structure data into a

structure format in such a way that the report generation in power BI becomes an

easy task all right so let's go ahead and get started off now in order to

transform the data we'll be using a new example for this time because

we'll starting right from the scratch and see how the transformation take place so let me show you the data that we are going to

transform quickly all right so this will be the data that we be working on now as you can see on your screen this is an Excel spreadsheet in which I have different cities

all together and I also have the revenue that they have generated in bikes

in axiaries and miscellaneous with respect to different years okay so as you can

see this is years and these are the revenue that they have generated for

each and every city all right so this is the data that will be working on now let's go ahead and

import this Excel sheet into our power BI desktop and let's go ahead and

add the transformation okay so let me quickly go to the power BI screen all

right so we are back to our power BI module now in this particular desktop we

will be going under edit query sections so that the power query editor comes up

and under this we have to import the data 建 order to import the data we have to go under the new source right now if you

click on this drop-down menu the very first thing which we can see is this

Excel tab so if you simply click on this Excel tab this is this file that we have

been searching for that is the multi-level spreadsheet I will simply click on it and

hit open the moment I hit open the navigator tool opens up or this pop-up

window comes into picture in which we have to select this sheet one which is the

data that will be transforming all right now you can see this is all the data that

which we were seeing on our Excel sheet as well I will simply hit okay now the

moment I hit okay you can see this is this data that has been imported from our

Excel sheet the way it can be seen out there as well okay now as you can see there

are some null values because there were few cells which were empty which has been

represented as null okay so we will will transform this less structure data into a structure format so in order to

transform this the very first thing which I will be doing is that I will go under

the transform tab and under the transform tab I will transpose this data the moment

I click on this transpose data see what happens now as we know transpose is nothing

but switching rows and columns that's what transpose does so it has exactly done the

same thing it has switched the column into different rows so this

these cities can be seen in different columns now it can be seen in different

rows in this power BI query editor all right the next thing that we have to do

is I have to give this column names this column some names aligned to it so in order to do that what we will be doing I will simply go on the extreme left side of your data

and I'll simply right click on it the moment I right click on it these are the

all changes that you can apply to this table but as of now what we'll be doing we

need to apply some headers so I will say that use the first row as header of this

particular table the moment you click on this as you can see now this 2005 2006 these are my different years which has been

applied as headers and right below it you can see the revenue also you can see

the column one has all the cities into it okay now let's go ahead and remove this

null as well so what I want is basically this null should be replaced by the city

name so I will be using a fill option which can be seen under the transform

tab itself under transform tab you have this fill drop-down menu if you can simply click on this I have down and up basically what is this fill

drop-down menu it is similar to the flash fill that we have in our Excel Microsoft

Excel as well so let me show you how what happens when I click on this down

button the moment I click on this down you see all the null has been replaced by the

city above it so it is very simple so you simply have to go here and simply select

the down button the moment you click on it you have now a structure data altogether let's go ahead and change the column name of

this so I will simply double click on the column names and name this as cities okay

I simply hit enter I have the city names also I have this as different categories

so I'll simply double click on it and name this as categories okay so I'll just

name it as category as of now okay now this looks a little bit structure manner

now the only thing which I want to do the changes is I want to

to convert these column headers into a vertical format in such a way like let me

explain you these are my different number of years and these are the revenue

generated by it with respect to different categories now what we'll be doing

instead of having so many columns will be just having two columns in which the

first column will tell you the number of years and the next the next column

which will be next to it will show you what is the revenue

along with it So what we are going to do is we will going to select all these together So I pressing the control key and selecting it one by one So this is in a pivoted format What we have to do is unpivoted in such a way that it looks like a tabular format so again I will go under the transform tab and I will simply

click on unpivoted columns the moment I click on unpivoted columns you can see

now the table has been changed in such a way that this is showing me number of

years as of now it is showing attributes so I'll simply double-click it and I will name this as years all right I hit enter

and these are my values right now this is nothing but the revenue all right and I'll

simply hit enter so this is now into a more structure format in such a way that I

have number of years and I have the revenue as well also I'll be going under the

home tab and changing this into a whole number all right so basically I've changed the

data type out here into a whole number now it seems like a structure data I

I will simply close and apply the changes and get back to our main X the power BI screen

It will take some time as it is applying the changes. Alright, now as you can see, we have this sheet which is being a, this is the query that we were working on

Now these are the different category. We have categories, graph, and the revenue. So if I just simply select any graph, let's say a normal column chart in which I will

want the city names all right I have the city names and I want to check how much is

the revenue along with it so I'll simply drag these two headers it says revenues

by cities altogether it's working so out here you can see this is the three different

cities that we were working on and the revenue along with it okay we can also create

a new report let's say a map report basically so I will simply go ahead under this

geographical condition a film map I want to create a film map so I'll simply click on this so now I

have all the categories so I have Seattle you have Vancouver and we have

Portland and what is the revenue as you can see we have dropped the cities and

we have dropped the revenue so it is giving me the information similarly if you want to go for a pie chart right so it is showing you all the different cities so

this is how you can transform your data in such a way that the report

generation in our power BI CSEC screen becomes an easier task isn't it simple guys okay so this is how it is done

and how this is how you generate a report in power BI okay guys so this was

all about this video hopefully you like this video see you until next time