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.

Next, let's add airline name information to the flight-delay data. With your main data source 2015_SUMMER_ONTIME active, select Merge Queries. In the dialog box that pops up, choose the query table with lookup information, in this case airlinecodes, and then click on the column header for the common column in each table: CARRIER in 2015_SUMMER_ONTIME and Code in the airlinecodes table.

Finally, choose what kind of merge/join you want -- they're described in the drop-down list. For example, the one we want, "Left Outer," means "Keep all rows from the first table whether or not there's a match in the second table." Power BI will now attempt to estimate how many matching rows there are, which is helpful if you've made a mistake and end up with zero matches and thus need to re-examine which columns you're using for the join and whether the data is formatted the same in both.

Everything good? Click OK and the join is done.

After the merge, you'll see a column on the far right of your data table labeled NewColumn, with a table icon to the left of the column name and arrows to the right. Click the arrows to expand that table column to view the data -- you can choose the columns you want to add to the original table.

005 powerbi expandnewcolumns

Expand the table column to view the data.

If you want to move that Airline column closer to Carrier, head to the Transform tab. There you'll have the choice to move the new (or any other) column. In this case, using the Power BI interface, you can select the new column and then use the Move > To Beginning and then Move > Right four times in order to get the Airline column next to the Carrier column. But there's a less cumbersome way.

powerbi transform

The Transform ribbon has a number of options including moving a column.

First, if you don't see a formula bar above your table, go to the View ribbon and check Formula Bar so it's visible. Then head to the Transform ribbon, make sure the Airline column is selected, and choose Move > To Beginning. You'll now see a formula in the M language that reorders the columns. Click Move > Right and you'll see how the formula changes. It looks like you could just cut and paste the Airline column where you want it to go -- but you can't make changes here.

powerbi formulabar


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.