Power BI for Office 365

Self-service analytics for all your data

Leader in Agile BI

Microsoft named a leader in Agile Business Intelligence by Forrester.

See the report

In the Cloud, on the Go

Share your analytics in the cloud and consume on the road with Office 365.

Learn more

See Power BI in Action

Interactive dashboards in your browser and stunning 3D visualizations.

See demos

Discover and Combine

Find, connect to, explore, and compile data easily from various sources.

Learn more

Model and Analyze

Bring the power of in-memory analytics and flexible modeling into Excel with Power Pivot.

Learn more


See your data in new ways with bold interactive visualizations in Excel using Power View and Power Map.

Learn more

Share and Collaborate

Create collaborative Power BI sites quickly to share live reports and custom data sets with your team.

Learn more

Get Answers and Insights

Ask questions and get back instant answers with the new Q&A feature.

Learn more

Access Anywhere

Stay connected from anywhere, on any device, with mobile access to your favorite live and interactive reports.

Learn more

Power BI Blog

  • 6 new updates in Power Query - July 2014

    In this post

    Download Power Query Update

    More flexible Load Options

    Query Groups

    Improved Error Debugging Experience

    Additional Query Editor transformations

    Options dialog

    Update notifications


    The July 2014 Update for Power Query is now available. You can download it from this page.

    This update is packed with lots of new features, so please take a look at the following video and the rest of this blog post. We hope that you like them!



    Here is a summary of the new features included in this release: 

    • More flexible Load Options for your queries.
    • Query Groups.
    • Improved Error Debugging Experience.
    • Additional Query Editor transformations:
      • Replace Errors within a column.
      • UX for defining Math operations based on a single column.
    • Options dialog: Restore Defaults & Tips for Cache Management
    • Update notifications: 3 notifications per update (max.), once per day at most.

    More flexible Load Options

    One of the most common areas of feedback about Power Query in the past has been the desire of having additional options to control how and where to load queries within your workbook. In this update, we're introducing a new Load Options dialog to customize how to load your queries. In addition to controlling whether a query should be loaded to the worksheet or Data Model, we now offer you the option to load to an existing worksheet instead of always loading to a new worksheet. It is also more clear now how to disable the load of a query (or to "only create connection" instead of downloading the results), which until now was only possible by disabling load to worksheet and load to Data Model.

    In addition to new options for how to load your queries, another area of feedback has been the need to have access to these options in all places from which users can load queries to their workbook. To address that, the following entry points have been added in this update…

    •From Search results:

    •From the Navigator pane:

    •From the Query Editor:

    •From the Workbook Queries pane and the contextual Query ribbon tab (to reconfigure the Load Options of an existing query without having to go back to the Query Editor):

    Query Groups

    Query Groups is a new concept introduced in this update that will help users better organize their queries within a given workbook, as well as perform bulk operations on all queries within a group (such as Refresh). Up until now, Power Query offered only a few capabilities in order to organize queries in the Workbook Queries pane, primarily moving queries up and down in the list.

    With this update, users can now select multiple queries using (CTRL + Click) and move them into their custom groups. Users can define as many groups as they want in the workbook, as well as groups within groups to create more advanced organization layers. This enables them to organize and classify the queries better within their workbook. In addition, users can leverage the context menu for each group entry to apply bulk operations to all queries within that group.

    Improved Error Debugging Experience

    In previous Power Query updates, we introduced some transformation capabilities within the Query Editor to "Remove Rows With Errors" and to "Keep Rows With Errors" within your queries. These features were helpful in order to discard all error rows or to narrow down to just the rows with errors in the final result, but didn't quite help users to see these errors in context, understanding the row from the result where these errors were introduced. In addition, after loading queries into the workbook, users would get in the Workbook Queries pane an indication of the total number of rows and the number of errors but weren't able to easily preview those errors from this pane.

    With this update, we've turned the "Number of rows with Errors" indicator into a hyperlink which brings up a preview of the rows with errors that users can explore and interact with. This preview also includes the row index to better understand where these errors appear.


    Replace Errors

    There are cases in which the way to resolve errors within your data is not to ignore the rows with errors but rather to replace the error values with a default value for the column. We're introducing the ability to do this: a new "Replace Errors" operation is available in the Transform tab inside the Query Editor. This option brings up a dialog asking users for the value to replace errors with for the selected columns.

    UX for defining Math operations based on a single column

    Very frequently, users find the need to add new columns to their queries that reference a different column and apply a Math operation. In previous updates, we introduced the "Add Column" tab which provides several operations that will create new columns based on one or multiple existing columns. One limitation until now was that, for Math Standard operations (Add, Multiply, Subtract, Divide, etc.), users were only allowed to select two columns, representing the first and second operators.

    In this update, we've added the ability to select just one column and use these Math operations. Users will be asked to provide the second operator in a dialog, and the result will be a new column added to the query with the selected Math calculation. This is available for all operations under the Standard dropdown menu in the "From Number" group, part of the "Add Column" tab.

    Options Dialog: Restore Defaults & Tips for Cache Management options

    If you followed our last two updates, you may be already aware that we have introduced new options for Custom Default Load Settings and Cache Management. This made the amount of choices in the Options dialog grow significantly, but there wasn't an easy way to "reset" Power Query to its default behaviors. Well, now there is such way…  : )

    In addition to the new "Restore Defaults" button, we have added a few tooltips to help users better understand the Cache Management options introduced in our previous update.

    Update Notifications Improvements

    As you may be already aware, Power Query has an Update notification mechanism that tells users about our updates every month. This notification was displayed to the user in the system tray every time that they launched Excel and there was an update available. While this works out great for many users, we also heard from some of them that installing these updates wasn't directly possible for them and they needed to notify their system administrator to perform this update and wait for a few days or weeks. At that point, seeing the Update notification continuously displayed in the system tray every time they launched Excel would become annoying…

    With this update, we've limited the number of times that a user will see the Update notification to three times per update (i.e. three times each month). In addition, we have also limited these updates to only be displayed once per day. We believe that this will establish a good balance between making users aware of our updates while also not reminding them too many times. : )

    That's it for this update... We hope that you enjoy these new Power Query features. Please don't hesitate to send us a smile/frown or post something in our forums with any questions or feedback about Power Query that you may have.

    Follow this links to access more resources about Power Query and Power BI:

    #update#power query#Power BI Training#Excel#Microsoft BI#data load options#query groups#update notifications#options dialog#transformations#error debugging

    Wed, 30 Jul 2014 16:00:00 GMT byMiguel.Llopis1 Comment

    Read post
  • Behind the Scenes: How Power BI Handled The World Cup

    In this post

    Importing the Data with Power Query

    Getting the right format with Power Query

    Power Pivot

    Power Q&A

    Crawl, then walk

    This year’s world cup was filled with unbelievable surprises; Miroslav Klose’s record breaking total goals, Germany’s thrashing of Brazil, Spain’s unfortunate crash-out, United States’ resilience through the Group of Death, and moments of Messi magic.  For the stat geek in all of us, Power BI Q&A made it easy to put the game in context and help you make better predictions about the outcome.  If you didn’t have a chance to ask Q&A about the world cup during the tournament, you can still give it a try here.

    We heard from many of you how amazing it was to see the breadth of questions Q&A could answer on the world cup.  If you’d like a glimpse into how we can teach Q&A a whole new sport in a matter of a day, read on!

    In this post we will discuss how we used the breadth of tools available in Excel and Power BI to bring this experience to you including:

    • Power Query to import and format Data
    • Power Pivot to build the massive model that supports the questions being asked
    • Power Q&A to actually answer the questions asked through visualizations

    Importing the Data with Power Query

    We partnered with Opta Sports, a leading provider of historical sports statistics to give Q&A all of the facts you’d expect a sports statistician to know.  The historical data was delivered as a series of XML files with the statistics for each game organized in folders by year. Each file contained information on the teams, coaches, referee, players, goals, cards, substitutes, and game statistics. This isn’t the easiest way to import data, but luckily Power Query has robust support for handling different data formats and folder structures.

    We used the From Folder option in Power Query to import all the data at once. Each row was a path to the individual XML file. Power Query also has the ability to read in the content of an XML file. Under content, clicking on “binary” lets you expand out all the data within a XML. After deleting and renaming a few columns, you end up with data for each game (see image below). As you can see, the value “Table” is in a lot of cells. That’s because Opta’s XML feed was highly nested, but it allowed for easy organization and navigation of the data. 





    We had to use a different approach for the 2014 data since it was updated in real time from Opta as the games progressed.  Using the From Web option in Power Query, it was easy to import the latest data from each game and refresh it whenever we wanted.   If you’d like to learn more about how to connect to almost any data source with Power Query, check out Getting Started with Power Query Part I.




    Getting the right format with Power Query

    Once we pulled in the data, we had to create a query for each table we wanted in our data model: games, goals, cards, referees, managers, stadiums, etc. The motivation behind creating a query for each table is that when we load each table to the data model, we can easily create the relational diagram required to make Q&A shine. Most queries involved expanding out nested tables, and filtering columns to get one query for one particular table. One functionality we would like to point out with Power Query is the advanced editor, which allows you full control over the transformation of you data. The entire advanced editor is not the scope of this post, but we wanted to highlight the scenario of pivoting tables. In the Power Query editor, under the “View” tab, there is a button to launch into the advanced editor (shown below):



    Chris Webb wrote a great blog post on pivoting tables.

    After each query was created for the particular tables we wanted, we loaded them to the data model for Power Pivot to consume.

    Power Pivot

    When you load to the data model from Power Query, it automatically adds your tables to Power Pivot. All we had to do in Power Pivot was match unique IDs to one another to create relationships so we know which players had goals, cards and which referees, teams, players, and coaches were in a particular game. Now that the relationships were created, we could take this to Power Q&A. 


    Power Q&A

    Now that we have our relational model created, we optimized our data by going through the steps outlined in the Demystifying Power BI Q&A Series (Parts 1 | 2).

    The model is now ready to be used on Power Q&A. However, we optimized the model further using the new cloud modeling environment to be able to ask a wider variety of questions. The cloud modeling environment also is an easy way to manage synonyms for your columns and tables. Since all the changes are saved on to the cloud, it allows everyone to reap the benefits.

    Documentation on how to get started with the cloud modeling environment can be found here. As an example on how to make a wider variety of questions to work, we created the following phrasing “aggressive team has large average cards per game”. The inclusion of this phrasing allowed us to ask more questions like:

    You can click the links above to see how one good phrasing allows Q&A to give really great answers!

    Crawl, then walk

    As you can see, there’s a lot more to teaching Q&A than just understanding natural language.  Finding the right data, choosing the right tables and relationships, and data modeling all contribute to a great experience in Q&A.  Luckily, the full suite of Power BI tools like Power Query and Power Pivot make it easy to achieve powerful results in a few minutes.


    #power query#Power BI#A#Q&amp#Power Pivot#2014 world cup#Brazil#football#soccer#World Cup#futbol

    Tue, 29 Jul 2014 16:00:00 GMT byPower BI Team0 Comment

    Read post