Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Introduction to the Power BI Desktop and the M Language


By:   |   Read Comments (3)   |   Related Tips: More > Power BI


Latest on-demand video "Optimize SQL Server Performance" (watch now for free)


Problem

The Power BI desktop designer is a great tool. However, the hardest part of any Business Intelligence (BI) project is gathering and formatting the data into reportable datasets. How can we get a better understanding of the internal workings of Power BI to have better reporting?

Solution

Microsoft has been working on self service Business Intelligence (BI) tools since the introduction of Power View for SharePoint 2010. All previous tools, Power Pivot, Power View, Power Query and Power Map, were add-ins to the Microsoft Excel application. Thus they were bound to the Microsoft Office software release schedule. Since changes are rapidly happening in the IT world, the Power BI offering was separated into the cloud offering and desktop designer.

Recently, Microsoft has released its latest desktop version for public preview in July 2015. The Power BI desktop designer can be downloaded from the following link.

The designer is based upon Power Query to import data into the model. The x-velocity engine is used to save the data in a space saving format. The imported datasets can be related via relationships. The report visualizations can be categorized into different dashboard pages. This part of the designer is based upon Power View. If you have worked with Power Query and Power view in the past, you will be right at home.

The data section of the Power BI designer is where you import and manipulate data. Just like SQL Server Management Studio which obscures the T-SQL generated by menu selections and actions, the Power BI designer is based upon the Power Query formula language informally know as "M". Having a deeper understanding of the M language will help a BI developer in cases where the menus can not.

Business Problem

An assistant professor at the Paris Technical college was kind enough to publish some sample datasets on his web site. The only restriction was to cite the authors (Petra Isenberg, Pierre Dragicevic and Yvonne Jansen) who cleaned up the data. Today's business problem is to load and clean up the CARS dataset.

Importing Data

The first step is to launch the Power BI desktop designer. The opening page gives the user references to forums, blogs and tutorials. Also, any recent data sources and designer files are listed for quick access in the left hand side. If you do not want to see this page again, make sure the "show on startup" option is unchecked.

Power BI Desktop

Close the opening page to see the designer with an empty workspace. The designer has three tabs on the left which are key to any project. The middle tab is the data tab where queries are written to load and clean datasets. The bottom tab is used to define relationship between datasets. The top tab is used to create dashboard pages that visualize the data.

Empty Power BI Workspace

Choose the get data option from the ribbon at the top. Select the CSV file option on the sub-menu.

Get Data in the Power BI Desktop

The Power BI Designer can handle many different data types. Since the purpose of these tips is to focus on data manipulation using the M language, the CARS dataset is a very simple comma separated value file. Browse to where you saved the sample data file from the above website.

Browse to View Data for the Power BI Desktop

Clicking the open button loads the data into a preview window. The user can either LOAD the data without manipulation, EDIT the data or CANCEL the current operation. Choose the EDIT option to manipulate the data using a set of steps in a query.

Preview Data Window in Power BI Desktop

Again, if you are familiar with Power Query, you will be right at home. The designer used to have the look and feel of the Microsoft Office Excel product. The color scheme was green and white. With the final release of the desktop designer product from the public preview, the screens now match the look and feel of the cloud based offering.

Look and feel of the Power BI Desktop

The preview of the data after loading from source shows the first two lines are not part of the data. Choose the use first line as header from the ribbon above. This will show up as a promote header action in the applied query steps.

Configure the header for the data on the first line

The second line of data describes the column types of the data set. Choose remove N top rows from the ribbon above.

Remove the second row of data which indicates the data type

Enter the number one to remove the unnecessary row.

Change the data types for specific columns

Many times numeric, date or time data comes in as text. We will want to change the data type in the model so that aggregations can be used in our dashboard reports.

Final formatted data in Power BI

All columns except for the cars name and origin have been formatted as an numeric type.

Configure the Query Properties for Power BI Desktop

The properties option on the ribbon above allows the designer to rename the dataset. Another nice option is the ability to add comments along with the query.

Power BI Desktop Advanced Editor includes all formatting and conversions

All the work we have done so far has been building up to this moment. The advance editor shows the M language statements used in our query. I do not suggest you abandon the menus right now and get out the specification for the M language. However, I will introduce custom (computed) columns in future tips. Having a strong handle on the language will allow you manipulate data when the menus will not suffice.

Load Data into the Power BI Desktop Model

So far, no data has been loaded to the data model. Choose the close and apply button from the above ribbon.

Data Tab in the Power BI Desktop

After the query designer window closes, we are place back on the data tab. We now can see the [dataset cars] with associated fields. Everything except for the text fields has a summation sign. This means those fields can be aggregated in dashboard reports.

Relationship Tab in the Power BI Desktop

Switching to the relationship tab, we can see the same dataset in a graphical object that one might find in a Entity Relationship Diagram. Since we have only one simple table, we can not build relationships at this time.

Summary

The Power BI desktop designer is a FREE and powerful tool that will allow an end user to quickly import data into the model. There are many options on the ribbon that can be selected to manipulate the data. The advance editor shows the user the actual M language statements that were create by the menus selections.

Most of the menu options correspond to simple manipulations. Complex formulas can be created by using the M Language (Power Query Formulas) within a custom (computed) column. Having a strong handle on the language will allow you manipulate data when the menus will not suffice.

Next Steps
  • Introducing computed columns to the cars dataset.
    How can we round numbers?
  • The earliest use of computers was to manipulate numbers.
    Can we perform advanced mathematical formulas?


Last Update:


signup button

next tip button



About the author
MSSQLTips author John Miner John Miner is currently a Microsoft Technology Solutions Professional (TSP) advising North East District (NED) corporations.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, December 17, 2016 - 12:20:44 PM - Stephen Back To Top

Great post, I am curious though... I have been to the Microsoft site that is the reference guide for M however, I need to have a more comprehensive source to use to develop my M language capabilities. 

Do you have recommendations on more comprehensive guide/book/pdf etc.?

Thank you,


Wednesday, September 30, 2015 - 8:38:45 AM - John Miner Back To Top

Hi Sasikumar,

This tool is evolving all the time.  With the September update, 44 new features were added.

The main purpose of Power BI is to have dashboards for monitoring key performance indicators (KPIs).

Unfortunately, there is no native way to print the dashboards or save the final data used by the report.

For now, use the snippet tool and save the image.

Since it is free, you can not complain to much.

However, they are taking feedback all the time.  I will make the suggestions.

Sincerely

John Miner

www.craftydba.com


Wednesday, September 30, 2015 - 5:43:20 AM - Sasikumar Back To Top

Really Power BI Desktop is very useful for data driven methodologies. Reports generated through graphs is looking good. But i supposed to save the generated report for my future analysis. I am unable to save the generated report in any format except its parent format. Is there any other way to sace like png, jpg etc..


Learn more about SQL Server tools