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 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. Martinez0 Comment

    Read post
  • 6 New Updates in Power Query

    Take Power Query to the next level: share your queries and create a corporate data catalog. Try Power BI for Free today!

    In this Post

    October is getting close to an end, but before we all put on our costumes and eat lots of candy for Halloween, we are very pleased to give you another treat: the October Update for Power Query is now available! This update includes several feature improvements that we hope you will like. Here is what is new:

    Download the Power Query Update

    What's New?

    New Transformation: Use headers as first row

    Column Name field in Merge Columns dialog

    Quick Access Toolbar in Query Editor dialog

    Warning for stale data previews in new queries

    Function Invocation from Search

    Preserve Excel Number formats in the worksheet on query refresh

     

    If you are using the Preview of the Salesforce connector for Power Query, you will also need to install this newer version after installing the October Power Query Update:  32-bit, 64-bit

    What's new in this update?

    1. New Transfomation: Use Headers as First Row.
    2. Column Name field in Merge Columns dialog.
    3. Quick Access Toolbar in Query Editor dialog.
    4. Warning for stale data previews in new queries.
    5. Function Invocation from Search.
    6. Preserve Excel Number formats in the worksheet on query refresh.

     

    You can watch the following video or continue reading the rest of this blog post for more details about each improvement.

     

    New Transformation: Use Headers As First Row

    Table.DemoteHeaders has been available for a long time as a library function. However, users would have trouble discovering this function and would ask very often about how to achieve this transformation. We’re trying to address this discoverability issue by exposing “Use Headers as First Row” in the Query Editor ribbon. The existing “Use First Row as Header” entry points (Home tab and Transform tab) have been converted from a button to a split button that now exposes “Use Headers as First Row” as its 2nd option.

    Column Name field in Merge Columns dialog

    “Merge Columns” now exposes a new field to specify the name of the column upfront. Previously, the only ways of customizing this name were via the generated step formula or by adding a subsequent “Rename Column” step. Over the next few months, we aim at exposing this option in other transformations that generate new columns as a result (such as Expand and Aggregate).

    Warning for stale data in new queries

    When connecting to data sources that the user had connected in the past, Power Query will try to leverage a local preview cache as much as possible. This has lots of advantages in terms of performance and UX responsiveness, however it is also an area of potential confusion for end users. Quite a few customers have reported that the “preview is wrong” where, in fact, it was simply out of date. Despite the Status Bar at the bottom of the Editor exposes the last updated date/time for a Preview, this was not prominent enough for many of these users.

    In this release, we’ve added a warning for the first time in each Editor session where a preview older than 24 hours is displayed to the user including an approximation for how old the preview is (at least, how long it has been stored by Power Query). Users can optionally click Refresh to update the preview. If the user decides that the cached preview is ok and starts working with it (i.e. adds a new step), the warning message will go away automatically.

    Quick Access Toolbar in Editor dialog

    The Query Editor dialog now exposes a Quick Access Toolbar. By default, this toolbar contains the Send Smile/Frown menu so that users don’t need to go back to the Home tab to reach out to us. In addition, users can pin their favorite actions from the ribbon to this toolbar, by right-clicking any of them.

     

     

    Function Invocation Experience from Search

    If you find a query in Search results that is a function, Power Query now lets you directly invoke it from the Online Search pane. Previously, users had to load the query to the workbook and then invoke it from the Workbook Queries task pane. This flow has been now simplified enabling users to directly invoke the query from Search. In addition to invoking the function, users can also add the function query to their workbook so it can be invoked multiple times. This can be done via a context menu option (“Add Definition”) available in function queries.

     

    Preserve Excel number formats on refresh

    Before this update, customizations to the Number format of Excel worksheet cells part of a Power Query table would be lost after refreshing your query.

    With this update, Number formats are not lost after a refresh. We look at the current and previous data type for each column and if the type didn’t change, we don’t remove the Number format of the output. This has been a very frequent customer complaint that is now resolved.

    Columns with Number format stay the same after refresh:

     

    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.

     

    If you are using the Preview of the Salesforce connector for Power Query, you will also need to install this newer version after installing the October Power Query Update:  32-bit, 64-bit

    Download Power Query from our official download page.

     

    #update#power query#Power BI#Excel#Query Editor#Microsoft BI#number format#function invocation#first row headers#column name

    Tue, 28 Oct 2014 17:35:00 GMT byMiguel.Llopis0 Comment

    Read post
  • 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). 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