In this lesson, we will understand the dynamic and powerful nature of Power Query, dabble a bit with Power Pivot data modeling and create simple visuals to analyze US population.
Author: Chandoo
My name is Chandoo. I am going to make you awesome in Excel, VBA, Dashboards, Power BI & Power Pivot and answering your questions.
I live in Wellington, New Zealand. It is a beautiful, small city on the southern edge of the world. I run my own Excel Consulting & Training business from here.
I am married to my college sweet-heart, Jo. We have 2 kids – twins. They are 10 years old now. They are busy creating new worlds with lego bricks or playing or learning. We (Jo &I) are busy with cooking, eating, walking, talking and reading.
View all posts by Chandoo
This is a great lesson. In addition to the how-to’s given, I found Chandoo’s mentoring along the Power BI journey useful for focusing thoughts and energy (for example, while working within the Power Query window, he explains that “our role within this window is not to analyze, but rather to define rules and conditions for cleaning the data”).
This also may be helpful for some others to know: For my instance of Power BI Desktop, I did not immediately see the shape map as a visualization option. However, I was able to enable this feature by simply checking the box for the Shape map visual under Options and Settings > Options > Preview features.
Thanks Thomas. Good tip on how to enable preview features. I will record video on preview features, custom visuals after new year and post it for the class.
Thank you – I couldn’t find the shape map option either, and your comment helped me find it.
Thanks so much Thomas Allan for your comment, because I couldn’t find the shape map either, I thought it might be a power bi desktop upgrade issue.
Thomas,
Very kind of you to share the steps to enable shape maps.
I was frozen at this step too, your comments helped a lot.
Thank you, Thomas. Very helpful. My Power BI Desktop did not show Shape map visual either.
In the Power BI Desktop version that I have, I am unable to load data from the website: http://worldpopulationreview.com/states/ . Below is the error I get:
Details: “Web.Contents failed to get contents from ‘https://worldpopulationreview.com/states/’ (308): Permanent Redirect”
Thanks Thomas for the tip on Shape feature.
Hi Ritesh & others… I think their website had a change. Please use this URL.
https://worldpopulationreview.com/states
Note the missing / at the end.
Hi Ritesh,
Try
https://worldpopulationreview.com/states
Instead of (using the last forward slash will new result in a 404)
https://worldpopulationreview.com/states/
I had a connection error when I tried to pull the data into Power BI. It may be due to the firewall rules of my company. It was helpful that you had the completed workbook available, I just opened that and then followed along with the video. I think the lesson was still effective even though I was unable to pull the data in myself.
Hi guys,
this is not exactly question about any step in this lesson, but maybe someone know.
I wanted to use population data for my country, I found a list here: http://www.gminy.pl/Rank/W/Rank_W_L.html
But I see that numbers there have spaces, which doesn’t work. For example there is 1 023 instead 1023, does anyone know how to easy remove spaces in all values using Power Query?
Clean up the population column so that there only number expressions.
Right click on the population column header and choose Replace Values.
In the top box (value to replace), enter a single space. Do not use quotation marks around the space. A single click of the spacebar will do.
In the bottom box (replace with), do not enter anything. You want to replace spaces with empty text.
Choose OK.
Below is a link to a Power BI file that you can download from m OneDrive you can use as an example. I will leave it up for a day or two.
https://1drv.ms/u/s!Aovu8_i-f2tphZZaFhQqLO70iGOzcA?e=CVBk2v
Thank you Thomas for your pointer to the State Map visualization. From now on I’ll check the discussion BEFORE spending so much time trying to find it myself.