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

Visualize

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

  • Getting Started with Power Pivot - Part I

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

    In our previous blog posts we explored how to bring data into Excel through Power Query (Part I and Part II - coming soon). In this blog post we will take that data and prepare it for analyzing and visualizing in Excel.

    To follow this tutorial you will need:

    1. Excel 2013

     

     

    In order for us to do this we need to load the data into the Excel Data Model. Loading data into the Excel Data Model allows you to use data in a way previously impossible in Excel. It allows you to create analytics and visualizations using multiple tables without writing a single VLOOKUP, load data that has more than 1.000.000 rows and add advanced business logic to your solution with Data Analysis Expressions (DAX) formulas.

    To load the data into the Excel Data Model you have three different methods, to load any table from Power Query into the Data Model you can select “Load to Data Model” and this will bring the data into the model. Besides Power Query there are several other ways to add data to the Data Model, you can use Excel’s get external data features or even directly import in Power Pivot. All these methods end up with the same result: one or more tables in the Data Model. In many cases this is enough to get started with your first Excel PivotTable.

    In this blog post we will take a look how to do analytics on top of some sales data that I loaded into the Data Model using Power Query. I imported invoice, date and a geography tables. To create my first PivotTable on top of this data I can get started immediately as Power Query already added my tables to the Data Model. Select Insert, PivotTable and selecting the Data Model as the connection. This creates a PivotTable with three tables in the Field list:

     

    I now open the Invoice table and select the RevenueAmount field.

     

    This will automatically add this field to the values area of the PivotTable and aggregate the values in the column in the PivotTable by using a SUM:

     

    Just the Sum of RevenueAmount by itself is not very valuable, I want to see this value for each region so I again check the checkbox in front of Region field in the PoliticalGeography table. This automatically adds this fields to the PivotTable on Rows:

     

    Unfortunately we see something is wrong here, the same value is repeated for each Region. Luckily Excel knows what is wrong and warns us in the Field list:

     

    To related fields from two different tables I can create a relationship in the model. I click Create to create this relationship. I then select the two fields between the two tables that I can use to create the relationship:

     

    This now automatically allows combine data from two different tables into a single PivotTable without having to do anything else (like writing a VLOOKUP to bring all the data together in a single table):

     

     

    When relationship between tables are already defined in the underlying database relationships will automatically be created in the Excel Data Model during Import. Also when you are not sure how to create relationships Power Pivot you can use Power Pivot to detect and create relationships automatically.

    In this blog post we created our first Excel data model, in part II of this blog post we will create additional relationships and start using DAX to add business logic to our Data Model.

     

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

    Wed, 22 Oct 2014 19:10:00 GMT byKasper de Jonge [MSFT]0 Comment

    Read post
  • Announcing Power Query support for Salesforce.com

    Today, at the Salesforce Dreamforce conference in San Francisco, we announced the preview of a new Salesforce connector for Microsoft Power Query for Excel. With this integration customers can now connect and work with data from their Salesforce accounts in Excel to build reports, visualize information and discover new insights. You can download this connector now (32-bit, 64-bit). Please be aware that for this preview to work you need to install the latest Power Query update.

    You can watch the following video or continue reading this blog post for more details about this connector preview

    After installing the latest version of Power Query and the Salesforce extension, users see two new sources in the From Other Sources ribbon menu. Like other data sources, Power Query connects on behalf of the user and only has access to the user’s data. The Salesforce API is used in a read-only manner, letting users access the data without making changes. The connection uses the Salesforce OAuth flow, and the user provides their Salesforce permissions to Power Query in order to connect.

     

    Salesforce Objects connects directly to the underlying object tables. Selecting Salesforce Objects displays a full list of all objects to which the user has access. Users can choose one or more tables and import them into the workbook.

     

    Salesforce Reports connects using the Reports API and lets users import reports built in Salesforce into Excel. This option lets users access data in a form similar to what they see in Salesforce, without having to connect to the underlying objects and rebuilding these views on top.

     

    Once in Excel, users can combine their Salesforce data with other relevant data using the wide range of connectors available in Power Query.

    Since this is a preview, the extension and feature set may change in the final released version. This extension is always tied to the latest version of Power Query, and may require users to update.

    Feedback is always appreciated, so please don’t hesitate to send us a smile/frown through the Power Query feedback options.

    #power query#Power BI#Power View#Power BI Training#Power BI for Office 365#Excel#Office 365#Getting Started#Microsoft BI#data sources#data visualization#hybrid#Salesforce

    Mon, 13 Oct 2014 21:00:00 GMT byPower BI Team10 Comments

    Read post