Subscribe / Unsubscribe Enewsletters | Login | Register

Pencil Banner

Free data visualization with Microsoft Power BI: Your step-by-step guide

Sharon Machlis | July 12, 2016
We'll show you how to analyze a file with more than two million records of U.S. airline flight delays in this hands-on tutorial with video.

You can start off working in Power BI desktop if you need its data-wrangling capabilities and then publish your data or report to your cloud account to create dashboards and share your work.

Data wrangling

Query Editor lets you reshape and transform data by, for example, merging multiple data sources and pivoting or unpivoting data. It can also handle common data drudgery like adding, deleting, renaming and moving columns.

For the flight-delay data, you want to combine multiple months into a single table, so don't start changing data within June until you've added July, August and September. Otherwise, you'd have to repeat the same transformations on each file before combining them.

To import more CSV files into this active Query Editor window, click on New Source > CSV and choose a file (you can select only one at a time), give the data preview a quick look and click OK.

Each data source should now be listed under Queries in the left panel. Next, in the Queries panel, click on the table that you want to add data to (for this example, you can choose 2015_06_ONTIME and add other months to that).

Click Append Queries (if your browser window is narrow, Append Queries may be under a Combine button), click on Three or more tables (the default is two tables) and follow the instructions in the dialog box.

004 powerbi appendquery

Append Queries lets you combine data sources.

Your 2015_06_ONTIME file should now have four months of data, not just June, so it's probably worth renaming it to something like 2015_SUMMER_ONTIME. You can right-click on it in the left-side Queries panel to rename it; or, with that query active (clicked on from the Queries panel), look at the Query Settings on the right panel and change text in the Name box under PROPERTIES.

Next, let's try merging two tables using a common column -- what's often called a join in SQL or using a lookup table in Excel.

The CARRIER column in 2015_06_ONTIME uses airline codes (such as AA) instead of airline names. It would be nice to have the complete airline names available since not all codes are intuitive -- AA may be obvious, but VX and B6 less so. I created a file of airline codes and carrier names, airlinecodes.csv, that's also available in the Computerworld Insider data download. You can import the file into the current Query Editor window using the New Source button.

On my system, airlinecodes.csv imported into Power BI without the correct column names. Instead, the columns were named "Column1" and "Column2" (obviously the system defaults) while the real column names, "Code" and "Airline," showed up as the first row of data. If that happens to you as well, look to the right on the Home ribbon and you should see an option to Use First Row As Headers. Click on that to fix it.

 

Previous Page  1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  Next Page 

Sign up for CIO Asia eNewsletters.