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 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:

    =DATE(

         LEFT([CalendarYearMonth],4)

         , 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] =

    CALCULATE(

    [Sum of RevenueAmount],

    PREVIOUSYEAR(DateTable[Date])

    )

     

    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%] =

       

              DIVIDE(

                      ([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]0 Comment

    Read 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. 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