Power Query Editor Assignments (3,4,5)

##powerbi ##powerquery ##analytics&insights

Nikhil More Dec 19 2020 · 4 min read
Share this

ASSIGNMENT NO 3:

1. Load data Wealthy.xlsx in to power bi and then click on transform data to enter into power query editor as shown below.

To replace the name of columns, double click on column name and change the names as per required names.

To replace word billion with empty string in billion ($) column click on the column billions and select replace values on menu bar.

To replace billion with empty string enter values as above image and click on ok
To replace $ character with empty character enter value as above and click on ok.

Now we need to convert the billions column to whole number so select data type as whole number as given in below image.

Above image shows the required transformed data.

Now we will plot a chart to see that the billion columns are treated as  whole number.

Final Dashboard Look

Conclusion and Insights:

1. As our goal in this assignment was to use power query and transform our data such that we are able to get right results so by converting billions columns to whole number which was earlier in form of text data, we are able to plot different charts.

2. Billions by sources of wealth -  Koch Industries has highest earnings of 120 billion $ followed by Amazon

3. Billion dollars and age by Name - Jeff Bezos with age of 54 tops the list.

4. Billion by name - Here too Jeff bezos with 120 billion dollar tops the chart.

5. Billions by Nationality - United States has highest earnings with 535 billion dollars as 7 out of 10 people belongs to US region.

************************************************************************

ASSIGNMENT NO 4:

1. Load GOT excel file in power Bi desktop and go to power query editor.

2. Rename columns as given in question as shown below.

3. Split Author columns in two and replace nulls with blanks.

Select Split column by delimiter and enter "&" as we need to split at "&" and click ok

4. Replace null with blanks

5. Now we need to filter the films directed by only Daniel as shown below.

6. Close and Apply and then in data view select date column and modelling tab select desired date format for eg: dd MMMM yyyy.

Final Dashboard !

We create a table chart, bar charts to see number of viewers by title and author and number of viewers by title and director.

We have plotted a bar chart to see a month with maximum number of episodes releasing.

Conclusion and Insights:

1. A seen for US viewers by title and author chart "The Dragon  and the wolf" has highest number of viewers and the author is David Benioff and this author has written maximum number of episodes and shown orange color bars.

2. From US viewers by title and director chart, The Dragon and the wolf tops the chart and  Jeremi Podeswa is the Director and her 2 more episodes "Dragonstone" and "The Red Women" are there in top 11 list.

3. Count of Season by month - This chart is added to see count of seasons that released month wise. As we can see May and April month has maximum number of season releases followed by June. This Chart can give idea of future episodes to be release as we can say that releasing chances of  future episodes/films in month of April and may might be higher.

************************************************************************

ASSIGNMENT 5:

The aim of this assignment is to use different column functions like custom column, conditional columns in power query editor.

1. Load LTD Sales excel file in power bi.2

2. In our dataset we don't have revenue column, hence as per 1st question we need to create a revenue column through power query editor.

3. To create this column, select custom column option from Add column menu.

4 Now, we have to create a formula as "Quantity * Unit price". so we basically have to add this formula in the custom column section and click ok.

5. We get a custom column so rename the column as revenue as we have generated this column using column quantity and unit price and change data type to whole number.

6. As per 2nd question we need to create another column with given conditions. so we add those conditions as given below.

Now for 3rd part we have to create condition based on product category as given in below image similar to one we created above.

Replace null with  “Non-Office Infra" in new product category column.

Thus we create 3 columns as a given in question.

Now we will create simple report from the data using our newly created columns

Dashboard Look !

Conclusion and Insights:

1.  After creating custom and conditional columns we create visuals from the data.

2. Revenue by state or province - New York has highest revenue.

3. Revenue by new product category -  Office infra contributes to 57.9 %.

4. Quantity by new product Category - highest quantity belongs to Non-Office Infra products i.e. 422.

5. Revenue by sales range - Maximum revenue of 1109217 which is above 25000.

6. We have added  revenue card and year slicer too.

************************************************************************

Comments
Read next