Analyzing US population with Power Query & Power BI

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.

13 thoughts on “Analyzing US population with Power Query & Power BI”

  1. 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.

    1. 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.

  2. 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.

  3. 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?

  4. 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

  5. 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. 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.