Power BI Blog

Tips, ideas, and updates from the experts

  • 6 New Updates to the Power BI Designer Preview

    Try what's coming next for Power BI!

    This week we are releasing a new version of the Power BI Designer Preview. This update is packed with lots of new things for you to try: from new connectors, transformations and Report view features to significant usability, quality and performance improvements.

    You can download this update from the Power BI Designer download page.

    What’s New in the Power BI Designer

    The following features have been added or improved in this update:

    1. Performance improvements.
    2. Dynamics CRM Online connector.
    3. Navigator Dialog improvements:
      1. Better preview experience for multi-dimensional sources (Analysis Services and SAP BusinessObjects).
      2. Show Selected Items option.
      3. Improved Search capabilities in the Navigator tree.
    4. New Transformations:
      1. Age and Subtract operations for Date/Time columns.
      2. Aggregate Columns: Option to disable new columns’ prefix.
    5. Field List improvements:
      1. Expand/Collapse tables.
      2. Hide/Unhide fields.
      3. Optimized layout (spacing, margins, and fonts).
    6. Report Pages Navigation - Keyboarding support
    7. Lots of other bug fixes.

    You can watch the following video or read below for more details about each feature.

    Performance Improvements

    We’ve made significant Performance improvements to the Power BI Designer in a couple of areas:

    -  Query Load: Performance of loading queries has improved by about 2x-3x in this release, according to our benchmarks. That is, queries that used to take ~10 minutes before this update, take now just 3-4 minutes. We encourage you to try your own scenarios and let us know if you’re still hitting performance issues.

    -  Excel Workbook Import: We’ve improved latency when connecting to Excel Workbooks from the Power BI Designer. You should see lower times to load previews, which will translate to a more responsive experience in the Query view.

    We continue working on performance improvements in various areas of the Power BI Designer. Expect to see more improvements coming in the next few months!

    Dynamics CRM Online Connector

     In this update, we are introducing a new connector for Dynamics CRM Online. Until now you were able to connect to Dynamics CRM Online using the OData feed connector, however, this was not very discoverable for the majority of our users.

    With the new Dynamics CRM Online connector, users are given guidance on what the URL of their Dynamics CRM Online feed looks like. User input is also validated and users won’t be able to connect until the URL they provide is correct.

    Navigator Dialog improvements

    We’ve made a few improvements to the Navigator dialog layout:

    -  Improved output preview for multi-dimensional sources (such as Analysis Services and SAP BusinessObjects): Output preview for these sources is now always visible as users select items from the tree. This makes it easier for them to preview the data that they will get when clicking Load/Edit Query.

    -  “Show All” vs. “Show Selected”: No more “Select multiple items” option. Instead, the default mode in the Navigator is to select multiple items. This makes it easier for users to select multiple tables. The “Selected Items” area at the bottom-left of the dialog is replaced with “Show All” & “Show Selected” options above the tree. This optimizes the space in the default view to display a larger number of items in the tree.

    -  Improved Search capabilities in the navigator tree: Search experience within the navigator tree has been improved. It’s now much easier to search for an item and click on it in the list or remove the search term. The position in the tree is kept so users do not lose the context for their selection.

    New Transformations

    A few improvements have been made in the Query view to make some transformations easier. These transformations were already possible via custom formulas, but now have been made much more usable:

    Age and Subtract operations for Date/Time columns: When working with Date/Time columns, it’s often useful to calculate the difference between two Date/Time columns (for example, Order Date & Ship Date), or to calculate the Age or Date/Time difference between a given date and “now”. These options are now available in the Date menu under Transform and Add Column ribbon tabs.

    Aggregate Columns – Option to disable column name prefix: An option to disable column name prefix has been added to the Aggregate Columns menu. This option was added a few months ago to Expand Columns but it was missing for Aggregate. With this option, users can decide upfront whether to include a name prefix based on the original column name to the Aggregate Columns output.

    Field List Improvements

    This month we’ve made improvements to the Field List based on your feedback:

    Expand/Collapse Tables: We’re adding the ability to expand or collapse tables in the Field List. This improves navigation and usability when dealing with multiple tables in this pane.

    Hide/Unhide/View Hidden Fields: Now you can also hide specific fields in the Field List to simplify this view. You can also look at hidden fields or unhide them (one by one or all at once).

    - Improved Layout: We’ve made some adjustments to the spacing, margins and fonts in the Field List to make it easier for you to work on it and handle more items.

    Report Pages Navigation – Keyboarding Support

    You can add multiple pages to your reports and navigate those pages using the left pane in the Report view. In this update, we’re adding keyboard support so it is much easier for you to move between pages:

    -  Up/Down Arrow keys: Use these two keys to move up and down in the list, one page at a time.

    -  Page Up/Down keys: Similarly to the previous item, move to the previous or next page in your report.

    -  Home/End keys: Move to the first or last page in your report with a single gesture.

    Bug Fixes and Quality Improvements

    Finally, we’ve also fixed lots of bugs and issues reported by many of you via “Send a Frown”. We encourage you to continue sending us feedback about the product and reporting any issues that you may find while working with the Power BI Designer.

    We hope that you enjoy this update. Stay tuned for more updates coming soon…


    #Power BI#Business Intelligence#data visualization#power BI Designer#Microsoft

    Thu, 19 Feb 2015 17:00:00 GMT byMiguel.Llopis10 Comments

    Read full post
  • BA Marathon: Free Day of Online Analytics Learning Spotlights Power BI, Excel

    Guest blog post by: PASS VP of Marketing Denise McInerney – a Data Architect at Intuit – began her career as a SQL Server DBA in 1998 and now applies her deep understanding of data to enable analytic solutions to business problems. She is founder of the PASS Women in Technology virtual chapter, and a speaker at user group meetings and conferences. You can follow her on Twitter at @denisemc06.

    On your mark… Get ready… Get set for a free day of online analytics learning at the PASS BA Marathon webinar series Tuesday, Feb. 3, kicking off with an inside look at the new Power BI platform, redesigned for data professionals, business analysts, and business users.

    Featuring six back-to-back live webinars with an exceptional lineup of business analytics and data experts from around the world, the BA Marathon will preview a slice of top speakers and sessions coming up at the PASS Business Analytics Conference, April 20-22, in Santa Clara, CA.

    The BA Marathon is excited to have Jen Underwood, Sr. Program Manager of Microsoft Business Intelligence & Analytics, and Miguel Martinez, Product Marketing Manager for Power BI, participating in the webinar series and sharing how business analytics and BI professionals can get more from Power BI and Excel.

    Check out the full BA Marathon lineup, and register now for your favorite sessions – or all of them! And make sure to follow the conversation at #PASSBAMarathon.

    Then see how to continue your analytics learning at the PASS BA Conference, with 60+ real-world sessions from these Excel and Power BI all-stars and more. Get the best rate when you register by Feb. 16 – and save an additional $150 with discount code BAMSFTBL150. I look forward to seeing you in Santa Clara! 

    #events#PASS#PASS Business Analytics Conference#PASS BA Marathon

    Fri, 30 Jan 2015 23:00:00 GMT byPower BI Team0 Comment

    Read full post
  • 6 New Updates in Power Query

    Try what's coming next for Power BI!

    We hope all of you are enjoying 2015 so far! The Power Query team has been busy working on the latest Power Query update, which includes a new connector (or many new connectors, in this case!) and a bunch of other usability improvements.

    You can download this update from the Power Query download page.

    In this update

    The following features have been added or improved in this update:

    1. ODBC Connector
    2. “From Azure” ribbon dropdown menu
    3. Workbook Settings dialog
    4. Option to enable or disable relationship detection
    5. Column & Row counts in Query Editor Preview
    6. Confirmation dialog to delete a query when deleting a worksheet

    You can watch the following video or read below for more details about each feature.   


    ODBC Connector

    This new connector can be found under “From Other Sources > From ODBC” and it allows users to connect to their generic ODBC providers. This makes it possible for users to bring in data via Power Query from several data sources that are not natively supported. Users can connect and import data from ODBC-based data sources by specifying the connection string parameters and a SQL statement to execute.


    Note that credentials should still be entered on the credential page, rather than in the Connection String field above, to ensure credentials are not inadvertently shared with the query. Similar to other SQL Statement connections, Power Query will not be able to optimize query steps after the SQL statement so it is suggested that you optimize the original statement as much as possible (i.e. include “group by”, “join” operations, etc.)

    “From Azure” ribbon dropdown menu

    With the addition of several new data sources in the past few months, we started to hit issues related to the amount of entries under the “From Other Sources” dropdown menu. We considered sending bigger, higher-resolution monitors to all Power Query users as a gift, so everyone could see all entries in this menu. However, given the large amount of Power Query customers, this was not a viable solution. As a fallback plan, we have decided to create a new top-level menu for Azure-based data sources to balance the number of entries in each dropdown menu. This does not add any new data sources, but improves navigation, discoverability and ease of access for our data sources lists in the ribbon.

    Workbook Settings dialog

    In this update, we’re modifying the way in which Workbook Settings are exposed in the Power Query ribbon. Instead of having Workbook Locale and Fast Combine directly in the ribbon, we’ve moved them into a new Workbook Settings dialog. This allows us to provide better descriptions about these two settings, as well as make room for a new setting that we are adding this month: Enable/Disable Relationship Detection.

    Option to enable or disable relationship detection

    When loading queries into the Data Model, Power Query will try to detect relationships between them. These relationships might exist in the data source (i.e. SQL Server) or might be implicitly created by the user as part of their query steps. For instance, merging two queries would make Power Query detect a relationship between these two queries, which would be automatically created in the Data Model when these queries are loaded.

    As much as this is a useful feature, it is also one that may cause an increase in the load time for your queries. In many cases, users either do not want these relationships or know that there aren’t any relationships beforehand, which make this additional wait time unnecessary. Based on feedback from many of you, we have decided to add an option to turn this relationship detection off. Note that this setting is workbook-specific, so you will need to modify the default behavior (default is to continue trying to detect relationships) for each workbook via the Workbook Settings dialog.

    Column & Row counts in Query Editor Preview

    We have added counts for number of columns and rows in the Query Editor Preview. This will help you get a better sense for the shape and size of your data while defining transformations in your queries. Note that these counts are based on the Query Editor Preview, not the full data set. In particular, the number of rows will be smaller than the actual table row count in most cases. You will see these numbers change as more or less rows are displayed in the preview (for instance, you will see the row count increase when scrolling down in the preview).

    Confirmation dialog to delete a query when deleting a worksheet

    Before this update, deleting an Excel worksheet would result in all queries that had been loaded to this worksheet being automatically deleted as well. This caused accidental data loss for some users, unaware of this default behavior. Based on feedback, we have decided to add an additional confirmation for what to do with queries when the Excel worksheet that they were loaded to is deleted. There are two choices for the user: delete queries or modify their load settings to disable load to worksheet.

    That’s all for this month. We hope that you enjoy this update and find the new features valuable for you and your customers. Please send us your feedback or suggestions via Smile/Frown in Power Query.


    Download Power Query from our official download page.


    #update#power query#Power BI#Excel#Query Editor#Microsoft BI#azure#ODBC Connector#relationships#columns#rows

    Tue, 13 Jan 2015 17:00:00 GMT byMiguel.Llopis4 Comments

    Read full post
  • New Power BI features available for preview

    Today we are previewing new features for Power BI, our self-service business intelligence solution designed for everyone. Power BI reduces the barriers to deploying a business intelligence environment to share and collaborate on data and analytics from anywhere.

    Try what's coming next for Power BI

    We are introducing a number of new Power BI features available for preview including dashboards, new visualizations, support for popular software-as-a-service applications, a native iPad app and live “hybrid” connectivity to on-premise SQL Server Analysis Services tabular models.

    These preview features are available for customers with a United States address. We’ll incrementally add new country support as we extend the preview globally in the coming months. Existing customers will find a preview option in their current Power BI sites. For those not currently using Power BI, you can sign up for a Power BI preview which includes the new features here.

    Now in Preview:

    Power BI dashboards

    Users can create personalized dashboards to monitor their most important data. A dashboard combines on-premises and cloud-born data in a single pane of glass, providing a consolidated view across the organization regardless of where the data lives. 

    Users can easily explore all their data using intuitive, natural language capabilities and receive answers in the form of charts and graphs. They can also explore data through detailed reports that target specific aspects of their business. Visuals from these reports can also be pinned to their dashboards for continuous monitoring. As part of this experience new visualizations have been added including combo charts, filled maps, gauges, tree maps, and funnel charts.  

    Out-of-the-box connectors for popular SaaS applications

    Power BI now provides “out of the box” connectivity to a number of popular SaaS applications. In addition to the existing seamless connection with Microsoft Dynamics CRM Online, with today’s release customers can also connect to their data in Salesforce, Zendesk, Marketo, SendGrid, and GitHub with many more to come in the months ahead. With an existing subscription to one of these services, customers can login from Power BI. In addition to establishing a data connection, Power BI provides pre-built dashboards and reports for each of these applications.  

    Excel BI and the Power BI Designer

    Excel 2013, which comes standalone and with Office 365 ProPlus, is Microsoft’s premier business analyst tool – it includes rich business intelligence features (Power Query, Power Pivot, Power View, Power Map) fully integrated with the powerful ad hoc analysis capabilities and familiar features of Excel – like Pivot Tables and Excel Charting. With Excel 2013, analysts can publish Excel Workbooks to Power BI, and share data, analysis and reports with Power BI users.

    For Power BI customers who don’t have Excel 2013, the new Power BI Designer provides a solution expressly designed for Power BI report creation. It can be used to import and model data, then author and publish Power BI reports to the Power BI service. 


    Connect live to on-premises Analysis Services models

    With the new Power BI connector for SQL Server Analysis Services, customers can realize the benefits of a cloud-based BI solution without having to move their data to the cloud. Customers can now create a secure connection to an “on-premises” SQL Server Analysis Services server from Power BI in the cloud. When users view and explore dashboards and reports, Power BI will query the on-premise model using the user’s credentials.

    With this hybrid solution, organizations can continue to retain, manage and secure their data on-premises, while securely enabling users to benefit from that data via Power BI.

    Stay connected from any device

    We are working on a wave of native mobile apps for Power BI. These apps will allow users to access their Power BI dashboards and reports through immersive mobile experiences for iPad, iPhone, and Windows tablets. The first of these - the iPad app is available today and can be downloaded from the Apple App Store, with other platforms following in the coming months. All Power BI mobile apps enable users to share insights and collaborate with colleagues so that they can take immediate action, from anywhere, anytime.

    We welcome you to preview the new features for Power BI and look forward to your feedback.

    #Power BI#Excel#data visualization#dashboard#Salesforce#power BI Designer#Marketo#Zendesk#Preview#Analysis Services#SaaS#Dynamics CRM

    Thu, 18 Dec 2014 22:50:00 GMT byPower BI Team21 Comments

    Read full post
  • Announcing New Excel 2013 BI Stability and Usability Improvements

    Recently we have announced a new public update to Excel 2013 that includes major stability and usability improvements in Self Service BI scenarios, especially when Power Query, Power Pivot & Power View are involved. For this release we eliminated over 120 bugs, crashes and hangs, and introduced two new important usability improvements that aim to make the analysts’ work smoother.

    If you are ready to get the update straight away, skip to the How to Download and Install the Update section below.

    Deferred Data Model Update

    In Excel 2013, any change to the Data Model caused Excel to immediately update every PivotTable, PivotChart, and Query Table in the workbook. 

    The users were forced to wait every time that they make a change to the Data Model, such as creation of a new measure or a calculated column. Sometimes, depending on the workbook and Data Model complexity, the waiting period could be pretty long. 

    To address this problem we have introduced the following changes:   

    1. When the user is within the Power Pivot add-in, we accumulate the changes performed by the user in the Data Model  without the Excel workbook being updated. 
    2. When the user moves the focus away from the Power Pivot window and in case the Data Model was changed, then the Excel workbook will be updated automatically.

    This change significantly enhanced the experience of the user doing modelling in Excel, so don’t miss on installing this update and experience it for yourself.

    Out-of-Memory Improvements

    Running out of memory in Excel 2013, in scenarios when the Data Model was involved, caused a computer freeze or Excel crash. In this release we targeted these scenarios and introduced a better and friendlier customer experience.

    Specifically, in the scenario when 64-bit version of Excel 2013 is running low on physical memory, or if the user is running a 32-bit version of Excel 2013 and getting close to the 2GB virtual memory limit, Excel will detect this, will stop all allocations and will show the “Out of Memory” error message to the user.

    Bug Fixes

    For this release we fixed over 120 bugs, crashes and hangs in different scenarios. We improved the stability of slice and dice operations, refresh, Data Model upgrade, connection editing and many more.

    We really encourage you to download the latest Excel update and try it yourself.

    How to Download and Install the Update

    If you have automatic updates enabled for your Office 365 subscription, you should receive the latest Excel update soon. If you don’t have automatic updates enabled in Excel, go to File > Account > Update Options > Update Now.

    If you are running a perpetual Office edition then you will need to install the following updates through Windows Update or Microsoft Download Center – KB2910929, KB2899498, KB2899505 and KB2920734.

    #update#power query#Power Pivot#Power View#Excel#performance

    Tue, 16 Dec 2014 17:00:00 GMT byPower BI Team18 Comments

    Read full post
  • [Webinar] Powering Your Business with Big Data and Business Analytics

    Update: You can watch this webinar on-demand if you register now.

    Big Data is big news, but what does it mean for you? With Microsoft, anyone can use Big Data to inspire innovation, accelerate decision making, and learn from and share insights—no supercomputers or PhDs required.

    Join us for this hour-long Big Data & Business Analytics webinar where we’ll discuss how to gain real business advantage with data across marketing, finance, sales, HR, and more. Get the insights you need to compete—to innovate, streamline operations, and tap into new markets.

    Learn how you can:

    • Inspire innovation by discovering new insights—without calling the IT department.
    • Make faster decisions with real-time access to massive data sets.
    • Learn from and share insights easily with rich visualizations of real-time data.

    Who should attend?
    Business leaders and business analysts focused on marketing, finance, sales, and HR who want to turn Big Data into a business advantage.

    Join us on Thursday, December 18 at 9:00am Pacific. REGISTER NOW!



    #Big Data#Business Analytics#Webinar

    Mon, 08 Dec 2014 19:00:00 GMT byPower BI Team1 Comment

    Read full post
  • 5 New Updates in Power Query

    Take Power Query to the next level: Try Power BI for Free today!


    We are happy to announce the availability of the November 2014 Update for Power Query. This update is packed with lots of new features that we hope you will enjoy and thank us for them (hmm, since we are in Thanksgiving week…).

    Download the November Power Query Update

    Before talking about new features in this update, we would like to invite you to complete a survey. It will take you only a few minutes and it will help us better understand what you like or don’t like about Power Query, how do you currently use it and what new features you would like to see in the future.

    Take the Power Query Survey

    What's new in this update?

    1. SQL Server Analysis Services connector.
    2. Salesforce Reports & Objects connector.
    3. Expand Columns – Ability to disable/customize column prefix.
    4. Improved File Menu options in the Query Editor.
    5. Entry point for “Advanced Editor” in the Queries pane inside the Query Editor.

    You can watch the following video or read below for more details about each feature.

    SQL Server Analysis Services connector

    With this update, you can now connect to Analysis Services cubes through Power Query. This connector has been long awaited for and commonly requested, and we hope that you like it. It provides exploration, transformation and import capabilities on top of AS cubes. Underneath the connection, Power Query translates user actions into MDX queries against the source. While the connector will also work for tabular instances, it is currently optimized for multi-dimensional experience. We hope to add 1st class support for DAX in the future.

    You will find a new entry under the “From Database” menu that will let you connect to your AS server. After connecting, you will be able to browse your cubes and pick the desired dimensions, measures, hierarchies or KPIs from them, filter and transform the data before loading it to the worksheet or Data Model.

    Salesforce Reports & Objects connector

    This month we’re including the Salesforce connector as a mainstream Power Query feature. This connector has been in Preview since early October. This feature allows customers to connect to their Salesforce accounts and import data into Excel. There are two entry points offered, including Salesforce Objects and Salesforce Reports. For a detailed description of these two capabilities, please check out this blog post.

    Expand Columns – Ability to disable/customize column prefix

    A frequent piece of feedback from our users is that they would like to have control over the column name prefix for columns generated by an Expand Column operation. Before this update, new columns would contain the original column name as prefix and users not wanting this prefix would have to manually remove it from each column afterwards (either via Column Rename or by modifying the generated Expand Column formula).

    With this update, we’re introducing a new checkbox in the Expand Column dropdown menu to let users opt out of this prefix.

    In addition to this check-box, we’re also giving users the option to change the prefix via the “Expand Columns” dialog.

    Improved File Menu options in the Query Editor

    We have added a few more options to the Query Editor File menu. Now you can access the Data Source Settings and Options dialogs without having to leave the Editor. Note that Workbook Settings are still not accessible from the File menu. This is something that we are trying to enable in next month’s update.

    Entry point for “Advanced Editor” in the Queries pane inside the Query Editor

    We’ve made it much easier to access the Advanced Editor dialog when working with multiple queries in the Query Editor. Simply right click the query that you wish to edit and select “Advanced Editor” in the context menu.

    That’s all for this month! We hope that you enjoy this update and find the new features valuable for you and your customers. Please send us your feedback or suggestions via Smile/Frown in Power Query.


    Download Power Query from our official download page.

    #update#power query#Power BI#Excel#Query Editor#Microsoft BI#Salesforce#expand columns#SQL Analysis Services

    Tue, 25 Nov 2014 17:00:00 GMT byMiguel.Llopis17 Comments

    Read full post
  • Top 10 Power Map tours

    We all know how beautiful and stunning Power Map videos are but what's even more striking is how powerful tours are as a storytelling tool to convey a message with Geo-spatial data.

    That is why we decided to put together a Top 10 list of Power Map tours and... drum roll... here they are!

    Visualizing Climate Change

    Lukas Steindl created this tour to visualize the intensity of tropical storms and population affected in the west pacific from 1945 to 2012. It also includes Twitter activity for typhoon "Haiyan" that hit the Philippines.


    World Cup History with Power Map

    Igor Peev, Senior Program Manager from the Power Map team created this tour that shows the history of the World Cup with data.


    Beer sales in Norway

    Alistair Brown created this tour to show the most popular beer brands in Norway.


    Thanksgiving Turkey Production and Consumtion

    Last year for Thanksgiving we wanted to answer the question, "Where do turkeys come from and where do they go?". This was the result.


    Walmart stores US growth

    Jason Strate created this very simple but powerful tour to illustrate the amazing growth and coverage of Walmart stores in the US since 1962.


    NORAD's Santa route tracking

    For the 2013 holidays, we put together this tour with the official NORAD tracking of Santa's route so we wouldn't lose him on Christmas eve.


    Windsurfing race GPS tracking

    Shish Shridhar shows us how it is possible to map different sailors routes from a windsurfing race.


    Best places to Trick or Treat

    The Office 365 team grabbed a bunch of public data and visualized it with Power Map to help us choose the best place in the US to go trick or treating.


    Casino customer traffic simulation

    Matt Smith simulates customer foot traffic and waiting times in a casino, showing us how the new custom maps feature can be used.


    Healthcare analytics

    This tour shows explores Geo-location health metrics such as disease frequency, gyms and fast food restaurants to drive better local policies for the public and private sector..


    If you have more top Power Map tours to share, please let us know @MSPowerBI and @MSExcel with the hashtag #TopPowerMap.

    #Power Map#Excel#tours#visualization#maps#geo location#custom maps#top 10#storytelling

    Thu, 20 Nov 2014 17:30:00 GMT byPower BI Team1 Comment

    Read full post
  • Machine Learning for Analysts: The Basics

    Machine learning is a hot trend in the tech and business press - you've probably heard about it, and you may be starting to explore it, or perhaps are already using it. OR you might have zero idea what to do with it, in which case, read on for some basics to get you started!

    So first of all, what is machine learning?  Simply put, machine learning is the practice of mining your historical data with computer systems to predict future activity - trends, behaviors, patterns, etc. From a benefits perspective, this predictive knowledge is huge. If you know where things are going, you can adapt your business and stay ahead.

    Historically, machine learning was an expensive endeavor requiring complex software, as well as deep expertise from data scientists. While many wanted to take advantage of machine learning, it was often too hard, time consuming, and costly to implement and operationalize.

    Enter the cloud.

    By putting machine learning capabilities in the cloud, we can eliminate many of these barriers and make these capabilities accessible to many more people.

    So why should you care? If you're a business analyst of any kind - financial, marketing, IT, operations, or any other department that's using data (so, basically all of them) - you can benefit from all that machine learning has to offer.

    Try it for free – no time limits, subscriptions, or credit cards needed – on www.azure.com/ml. To help you get started, there are several pre-baked sample models that can help you explore some common business scenarios for machine learning:

    Check it out, and let us know what you think.

    If you’re interested in stepping through more detailed machine learning tutorials, explore these videos:

    If you’re already exploring machine learning and perhaps know a bit of R, there’s a free webinar happening on Friday Nov. 14 at 9:30am PDT that might be of interest to you: Operationalizing R as a Web Service. Register now!

    #Machine Learning

    Tue, 11 Nov 2014 17:00:00 GMT byPower BI Team2 Comments

    Read full post
  • Getting Started with Power Pivot - Part II

    Welcome back to our Getting Started with Excel and Power BI Series.

    In Part I of Getting Started with Power Pivot we created our first Excel Data Model. Today we will create additional relationships and start using DAX to add business logic to our Model.

    To follow this tutorial you will need:

    1. Excel 2013



    After making the data show up using the right relationships in part I, we want improve the Pivot Table as we want to slice my data by year. To do this I'll add a slicer to my worksheet:


    Clicking the slicer doesn’t do anything yet as there again no relationship between the Invoice and DateTable. After inspecting the fields in both tables I find out that there is no field in the Invoice table that I can use to relate with. Luckily we can create a field in the table that allows us to create the relationship.

    To work with the Data model In Excel 2013 we use the Power Pivot add-in. This add in is installed by default in many versions of Excel, you just need to enable it.

    Now that we have enabled the Power Pivot add in a new ribbon will show up in Excel:


    To work with this model I click the “Manage” button, this will open the Power Pivot dialog:


    The initial dialog that opens shows each tables in the Model with all the rows of data, in this case it shows the DateTable. You can switch between tables by clicking the table tabs at the bottom:


    I now select the Invoice table where I want to create a column that adds a Date value to for each row of data.  I do right mouse click on the CalendarYearMonth column and select “Insert Column”.


    This creates a calculated column in the Invoice table where I use a formula to populate the column with values, I combine values from the CalendarYearMonth column to create a new date value using the following formula:



         , RIGHT([CalendarYearMonth],2)

         , 1)

    As you might notice the formula used here is very similar to the formula’s you would write in Excel, this on purpose, the DAX language used in Power Pivot is designed to be familiar to the Excel users. It has some differences that are mostly focused around capabilities that are not supported by Excel like traversing relationships and special built-in functionality for calculations.

    Adding the calculation to the data model will give us a new column that we can use in the relationship:


    I then rename the column to InvoiceDate. To create the relationship I can also move over to the diagram view in the Power Pivot add-in and drag and drop the fields that I want to create a relationship between. Dragging and dropping results in a relationship being created:



    If we now go back to our PivotTable and we now see the results we expect:


    Now I want to compare the RevenueAmount for the selected year with the RevenueAmount for the previous year. In order for me to do this I can again use a DAX formula. In this case I want to create a calculated field in Excel to add to my PivotTable. 

    I go to the Power Pivot ribbon and click Calculated Fields, “New Calculated Field”:


    This opens a new dialog that allows me add a new DAX formula that leverages some of the special DAX time intelligence formula’s to calculate the “Sum of RevenueAmount“ for the previous year:

    [RevenueAmount PreviousYear] =


    [Sum of RevenueAmount],




    This will add a new field to the PivotTable that shows the RevenueAmount for the previous year compared to the Year selected in the slicer:


    Now I can add another calculation that will calculate the Year over Year Growth in percentage:

    [Sum of RevenueAmount YoY%] =



                      ([Sum of RevenueAmount]                                                                

                        - [RevenueAmount PreviousYear])

                      ,[RevenueAmount PreviousYear]


    In this calculation we divide the Sum of RevenueAmount minus the RevenueAmound previousyear by the RevenueAmount previousyear that results in the percentage growth. Adding this calculated field to the PivotTable gives us the percentage of growth for revenueamount:


    I hope these blog post gave you a good overview of the capabilities of Power Pivot. Having said that this blog post will be just the beginning of your journey to getting to know Power Pivot and unlocking all its wonders.


    #power query#Power Pivot#Excel#data#Getting Started#vlookup#model#pivot table

    Thu, 30 Oct 2014 16:00:00 GMT byKasper de Jonge [MSFT]2 Comments

    Read full post
  • Getting Started with Power Query - Part II

    Welcome back to our Getting Started with Excel and Power BI series.

    Today we will continue with our first Excel feature, Power Query, which allows you to discover, shape and transform your data before importing it into Excel or your Power Pivot data model. In Part I we covered mostly how to connect to different data sources and some quick transformations for your newly found data.

    In this post we will cover:

    To start this tutorial you'll need:


    OK, let's get started. Open the provided Excel file at the end of this post. You can follow the getting started steps with the video or with the text on this post. Once you open the file, select Power Query from the ribbon. If you are on one of the imported tables, you will see the Workbook Queries menu on your right. If you don't, just click the Workbook Queries button on the Power Query ribbon.

    First let's do an online search to import and edit its query so we can learn how to create and modify formulas:

    1. Create and modify a simple formula

    1. Select the Power Query tab and perform the following online search: "largest cities south america"

    2. Hover over the public data results: 'Largest Cities..." and select edit, which will take you to the Query Editor

    3. Click on the fx icon right next to the formula bar

    4. Type the following formula = Table.AddColumn(Source, "Country Code", each Text.Start([Country], 3)) and click on the check symbol to the left of the formula bar - This formula will extract the first 3 characters of Country and place them in a new column

    5. You can modify this formula directly in the formula bar or selecting in Applied Steps the step you want to edit and clicking the gears icon

    2. Filter, sort and group data

    Filtering on the Query Editor is very similar to Excel. For any given column you can click on the down arrow and start filtering or sorting right away. In our specific case: 

    1. First, let's try automatic filtering. Select the Country column, click on the down arrow, deselect (Select All) and choose Brazil, Chile and Ecuador.

    2. When you click OK, you'll have the filtered list.

    You can also use the same menu to sort your data (ascending or descending). 

    Now, let's try grouping by country:

    3. Right click on the country column header and select "Group By"

    4. You will see the Group menu. In this case, we'll just count how many records we have by country. You can also perform other grouping operation such as sum, average, min or max on a field (in this example we could do that with population):




    4. Work with columns

    You can perform almost any type of operation with columns on the Query Editor to shape and transform your data any way you like it. We did that on step 1 with a simple formula. Let's try other transformations available within Power Query.

    1. First let's remove columns that we don't want to use. In this case, holding down the Ctrl key select the Image and Key fields. Then right click on one of them and select Remove Columns on the menu:

    2. Now, let's create a Key with our country code and the city name. In Excel, this would be a text operation with the "&" symbol. In Power Query, this is the same. Click on add custom column on the column menu.

    3. On the add custom column menu, write the following expression: [Name]&"-"&[Country Code]. We are joining the city name and the country code with a dash in the middle. Rename the column to "Key"

    4. You will see the newly added column after you click OK and the function being showed in the formula bar. The Power Query Formula Language provides a lot of flexibility to shape and transform your data. You can find more information and references here. In any case, you can use the Query Editor to save these steps through the UI and Power Query will take care of the formulas for you.

    5. Finally, you will notice that Power Query is saving all of these steps to be replicated on your data refresh or modified in the Applied Steps panel

    5. Combine data

    There are several ways to combine data in Power Query. We will take a look at 2 of them: Append and Merge

    1. First, we will merge this query with another one that has the country population on it. The file provided already has another query (found with Online Search) with the population of countries called "Population by Country". Click on the Merge button on the Power Query menu.

    2. On the merge menu, select each query and then click on the country field for both. This will be the the field that will define the merge so we can get country population on our original table. Also select "Only include matching rows" so we get population just for the list of countries we have. Before clicking OK Power Query already tells us that we have 40 matching records. Click OK

    3. You will get a new Query Editor window with a new column. Click on the corner of the header for this new column and select the fields that you want to merge. In this case, we want population. Then click OK.

    4. Now, we have a new table with all the information we had before, plus the population of the country for each record. If we click on close and load, we'll import this data into Excel.

    5. Now, let's try to append data to our original table. There's another query in the file called "Largest Cities in Europe". This query already has the same structure as our original table after adding Country Code and Key, including column names. We achieved this using the Query Editor on a similar online search. You can review the steps editing this provided query. We'll append this data to our first table. Click on the Append button on the Power Query menu.

    6. As before, select the 2 queries that we want to Append ("Largest Cities in South America" and "Largest Cities in Europe") and then click OK.


    7. You will get a new Query Editor window with the appended queries.


    6. Share queries

    Finally, if you signed up for a Power BI trial, you have the option of sharing any of these queries with the rest of your organization. As long as the user has access to the underlying data (in this case public data) they will be able to search for the query with the Online Search feature within Excel and load the data from the query you shared. Let's share the first table we created: "Largest cities in South America"

    1. First of all, you need to be signed in to your Power BI tenant. On the Power Query ribbon, click on the Sign In button

    2. Enter your Power BI / Office 365 credentials and sign in

    3. Now, let's make sure the name and description of our query is accurate so other users are able to find it. On the Workbook Queries menu, right click on the "Largest cities in South America" query and select edit

    4. Once your are on the Query Editor, click on All Properties on the Query Setting menu on the right. The you will see the query name and description

    5. Everything looks good, so let's share the query to the Data Catalog. Close the Query Editor and right click again on the query. Then select the Send to Data Catalog option

    6. You will get another opportunity to edit the name and description. You can also add a documentation URL in case people want more info or request access to the data. Click on the Sharing menu. Here you can decide who will have access to query you're sharing. In this case we will go with Everyone in the enterprise. Click Send.

    7. Now, your query is live on the Data Catalog and discoverable by anyone based on your description. Let's do an online search for it and filter by organizational data.

    8. Finally, if you want to edit your shared queries you can click on the My Data Catalog Queries button on the Power Query ribbon. From this menu you can delete, load, update and even view statistics for your query.


    #power query#Excel#data#Getting Started#files#data base#data sources#group#filter#share query#sort#column#shape data

    Wed, 29 Oct 2014 16:00:00 GMT byMiguel A. Martinez1 Comment

    Read full post