Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"

Power BI Desktop September 2015 Update

0 (0 Likes / 0 Dislikes)
This video, we're going to take a look at the new features included in the Power BI Desktop September 2015 Update. We have a massive update this month with 44 new features. Yes, I said that correctly, we actually have 44 new features which, if you count the number of working days that we've had since our previous update in August, it comes down to something like almost two new features per working day. So we've actually been very busy. In fact, we have so many new features this month that I had to split my use of PowerPoint slide with the feature list into four different slides this time around, and I've grouped them by the major feature areas in the products. So you'll see a lease for Report Authoring Improvements, another one for Data Modeling Improvements and two more lease for data connectivity and Data Transformation capabilities. So let us start by looking at the Report Authoring Improvements. The first improvement on the list is the ability to define report-level filters. So, before this update, users were able to define filters at the visualization and page levels, but it was not possible to define a filter that would apply across all of the visualizations in all of the pages on your report. So let's take a look at report-level filters in Power BI Desktop. I've switched to this existing report that I have with sales information, and now you will notice how we seen the visualization span, where they used to be the page level filters. We've also now added report-level filters, so you can drag any fill within your fill list, so let's say, for instance, we'd like to drag the country and you can define a filter here that would apply across all of your pages, so in this case, I'll just have one page but if I were to exclude Brazil from my report, you'll see how it will actually exclude Brazil from all of the different visualizations within all of the pages in my report. All right. Great. So let's go and take a look at the second feature within the Report Authoring Improvements this month. The second feature is drill up and drill down for columns and scatter charts. So it is now possible to define drill up and drill down behaviors for columns and scatter charts within your reports. These behaviors allow users to enable the consumer of a report to see aggregated data and to be able to drill into a detailed breakdown of that data. For instance, let's say that you create a sales by country column chart, like the one that I have here in Power BI Desktop. So let's say that you have total sales by country and then you would like to actually define a drill down behavior that when you click, it will drill down to the total sales by city. So I have my country fill here in the axis, in the axis area and I can also drag city and I can put it below country, so now I have double axis here with country and city, two different fields in the axis, in the X axis. And now I can see how, on the top of my visualization, I got three new controls. One is for drill up, which of course, right now, it doesn't, it's not enabled because I'm in the top level visual. And then the second option is the ability to drill all to the next level, so this will basically take all of the data that I have for sales across all of the different countries and it will just break it down by cities. So if I do that, it's going to do total sales by city and you see how, now I get a bunch of cities across all the countries, so this is basically all of our sales by city. And then I also have the ability now that I'm down to this level, I could also drill up, with the first control that was disabled before, so now that I've drilled up and I'm back to the total sales by country, I'd be able to enable the drill down mode so when I click here, on the top right, I'll click to enable drill down and you see how the arrow now is selected, so that means that drill down mode is enabled. And so, for instance, now if I click on USA... I'm drilling down to see the total sales by city within the United States. Note that in this mode, it's also possible to still do cross filter, so, for instance, I could select year 1997 and I would do the cross filter across all of the other charts, including the one in which I'm drilled down and that, we've got the same across all level, so if I go, again, one level up, it's going to still show me the cross filter state, based on the year that I selected in the other column chart. Great, so let's go back to the list of features. The third new feature is actually two different features in one. We have added the different options for page sizes and page view options for your report. So let's take a look at that feature in Power BI Desktop. So the first one that we're gonna look at is page sizes which is available from the ribbon and the report or also if you select the background of your page and you go into the formatting pane, you're going to see section for page size options, where you have a few more. So this page size controls the size and the aspect ratio of each page in your report. Keep in mind that you can actually a different page size to each of your pages within the report, so if I were to create page two now, I could assign a different page size or aspect ratio than the one that I have in page one. The available page sizes are 16/9, 4/3, Dynamic selection, Letter and Custom, so, for instance, if we switch these to Letter page size and you see how my actual report canvas readjusts to the new page size and then I can reorganize everything here so that it actually looks nice in this page size. I can switch it then back to 16/9, so I'm going back to the default page size that I have for my report, but you get the idea, right? It's fully flexible because then you could also have different sizes, depending on, for each of your pages depending on the content of your page. Now the second group of options is page view. So with page view, you can actually configure how your report pages should be render, depending on the size of the report viewer, so whether you're in Power BI Desktop or in the Power BI service, you'll be able to control the different behavior. So, for instance, let's go and look at the different page view options which are also here in the ribbon. I'm able to specify a fit-to-page behavior which is the default that I have right now, or I can also say fit-to-width and you see how now, it's little bit wider but it's also a little bit taller, so I get this scroll bar and I can then scroll up and down to see the full contents or I can also turn this into the actual size, which is going to make it even much bigger in my view, so I get also horizontal and vertical scroll bars, in this case. Of course, if I start doing things like collapsing the ribbon or collapsing some other things, I'm going to be able to actually see the full size, like now. So just a few more options for you to configure your report and optimize it for the content that you would like to expose to your users and make it much more efficient for you to create these wonderful reports with Power BI Desktop. All right, so that's it for the third options, the third new feature of page size and page views. Let's go into the next group. We're adding support for inserting basic shapes in the repot canvas. So let's take a look at this. Let's actually make a little bit of room for inserting some shapes, I'll make this visualization here a little bit smaller. And now we've added, in the ribbon, the ability to insert shapes. So we have support for five different shapes, rectangle, oval, line, triangle and arrow. So let's go and insert, for instance, an oval. And there it is and now I can of course resize it and then I get a few additional format shape options here on the side. So, for instance, I could control the color of the line that's basically surrounding this shape or deleting the shape. I can also control the transparency and the weight, whether I would like to have fill in the image or not and if I do have a fill, like I can also control the fill color... as well as the transparency. I can also lock the aspect so, let's say that I want to lock it. Now, if I can make it bigger, it actually keeps the same aspect ratio, so it doesn't let me make it more wider or taller without making it proportional. So what other options we have. We have the ability to rotate images, also in shapes I've reinserted, so you can set either the specific value or just use the slider to actually rotate it to the actual rotation level that you'd like to have. Finally, you can also, just like with any other report element, you could control the title and the color of the title and the position, as well as the actual background for the image, or in this case, for the abounding box that contains the shape. All right, so that gives a few additional options to format your reports. And we're also looking into adding additional support for other types of shapes in the future, so if there's something here that you're missing, please let us know and we will definitely iterate on that and make incremental improvements. Now, let's move on to the next feature on the list. This is actually a couple of improvements to the fields pane. These are based on feedback from many of you, that navigation and being able to find items within this pane was a little bit and I'll make one of my jokes, it was a little bit painful before. So these two improvements are, in first place, a new search box, so you can actually find fields by name. So let's say that I'm here on the fields pane and I would like to find something about customers, so I can now just start typing something like customer and it would narrow down and highlight the tables and fields that contain a match for the search thing that I'm pasting in. Now, the second improvement is the ability to expand and collapse all of the items. So before it was possible to expand and collapse individual tables. Now, if you right click in one of the tables, you can also get access to collapse all and expand all, so it really vastly improves the usability of this fields pane, as you will see as you start using it. Now, the next item, it's also improving the interactivity between the field well and the fields pane, so you can actually drag multiple items for the buckets with the visualizations pane. And for those buckets that actually support a single item, you're actually able to drag a single item to replace it when there is already an item on it. So let's take a look at it, for example. I'm going to select the total sales by category here. And I'm going to go back into the visualizations pane. So you see how I have my axis at the category name and the Legend. It's empty right now so let actually drag something to the Legend here. Let's drag the category name as well. Okay, so now we got our Legend over there with category name. And so now, this is a perfect example, the Legend, it's a perfect example of a field that actually or a bucket that actually only supports one field at a time. So if I drag a second field like, for instance, country to the Legend. Before this was not possible at all, but now it actually replaces the previous field as you can see and it populates it with the actual country names as the Legend and they actually updates the color, of course, on my different bar chart elements. So before, you actually had to remove the field in order to be able to drag it over there, which, in some cases, it would mean that the visual would be automatically deleted for you if the bucket that you were cleaning out was the last one that had an element on it. So this greatly improves usability of the interactivity between the visualizations and the fields pane when it comes to configuring the different buckets for each of your visualizations. Great. Let's go back to the list. We're on to the second column now. So we've added additional formatting options for data labels. Let's go back to one of my charts. So let's select total sales by country over here and it's going to the formatting pane. So now if I go into the data label section, I have additional options like the color of my data labels, so let's say to something more like red or pink, like that. And you can also adjust the display unit. So in this case, we're using automatic, but I could also switch this to the thousands so, and you'll see that then we'll go from millions to thousands, as you see, now it says K at the end, or you could also configure this to something else like billions, maybe. It's going to be too small. So the default is auto because we'll try and pick the best option for you, based on the actual values in your data. But, keep in mind that you can always configure this and we also provide an option for adjusting the precision or the number of decimal places, your numbers, in your data labels, so let's say to three like that. And you'll see how it automatically outdates the data in your data labels in your visualization. Great. So the next item on the list is some improvements to the Cartesian Axis in your charts. So these are improvements to the X and Y-Axis. The first one, it's about the label directions for the X-Axis. So we will automatically adjust them to be horizontal or diagonal, so that the feed better in the space that the chart was given. So, for instance, in this example, you see how I had lots of countries in this count chart. Let's remove the shape because it's getting on the way. So you can see how I got lots of countries and some of these country names are long enough that they wouldn't fit if they were horizontally positioned, just like the years in this other chart, they fit just nice and great because they're just small numbers and there's very few of them. So in this other one, we actually automatically turn them to diagonal, so that they'll fit better and be able to display your data in a better, optimized layout. The second item is the support for Logarithmic and Linear scales for values in the Y-axis. So you can actually configure these if you go to the formatting pane and you select the chart, of course. You can go to the formatting pane and then go into the Y-axis section. So now, you have a couple options for linear and logarithmic scale type for you Y-axis. All right. The next option is the ability to set the hyperlinks on textboxes on top of a displayed text. So basically, before this update, users had to always have the hyperlink be-- basically the link that gives the hyperlink, it will have to be visible, so if you were to put www.powerbi.com, you would actually see www.powerbi.com in your textbox. Now, we've improved that so that you can set friendly text or displayed text for any hyperlinks. So, for instance, let's go and say more info would be the friendly text that I would like to have. And now I can select this text. And I can use the insert link option within the flying menu and type in the url that I would like to go, so, for instance, let's say powerbi.com. And at the end I'll hit done. And now this has been linked, so it actually when I mouse over it then I try and click it, it will bring up this fly out menu and here, I can click the actual url, that would take me to that website or I could remove the hyperlink from here as well. So it makes it more efficient for you to have some text that it's descriptive about what the hyperlink is about, but you don't have to put the actual url in front of the user at that point. Great. The next group of improvements are improvements to existing visualizations. So now, we have made a bunch of improvements here across different visualizations and we have different categories for them. So let's just start by talking about table and matrixes, so we actually enabled the ability to resize columns within a table or a matrix visualization. On tree maps, we actually support now data labels. On slicers, we support the ability to select all or unselect all with a single click within visualizer control. And we also added additional formatting options for visualizers like the outline and background, as well as being able to format the title or the font color and background of the header and the rows, fonts for them. So for a scatter chart would be another one that we've improved. Now, you're able to configure the bubble's color stroke for cards, single cards, we support a bunch of formatting options that were available before for others but not for single cards. So, for instance, the ability to configure data labels, category labels, the title, the background and the word wrap options. For combo charts, we've actually improved the performance so they will-- your combo charts will render faster and also the interactivity of them. You'll see that they're more responsive there's, there-- smaller or lower latency when you click on them, when you interact with them, so they re-pane themselves. And the last visualization that we've improved with this update is the gauge. So you're now able to customize the main marks and target values just within the field pane, the visualizations pane, you'll be able to drag any fields from your report to actually become the main marks and target values for your gauge. Now, the next item on the list is the support for displaying color saturation values in tooltips. So let's take a look at that. So I have this combo chart that we've been working with and I'd like to add field to the color saturation. For instance, I'd like to add the total number of units sold which is quantity from all the details of the color saturation and you see the color saturation now. But what we've added now in the tooltip is that you will actually see the value of the field that will say that's color saturation. So now, you see quantity as the additional value with the tooltip over here, the last value, whereas, if I didn't have the color saturation, of course, you gonna see that value, you want to see the other two that we're defining in these visualizations. So little improvement that would make it easier for you to understand your charts as you look at them and as you-- also where some of the columns and bars on your charts. Now, the last improvement within the report authoring area for this mass is the ability to resize images and to also apply additional formatting options. So let's go and take a look at this image that I have inserted over here. If I select it, now I get this new format shape pane where I can configure things like locking the aspect, so if I unlock it, I'm going to be able to freely resize these however I want. But if I lock the aspect, again, I'm not gonna be able to do that and I'll always make it proportional between the height and the width. Now, the next option that we've added for formatting shapes is the ability to define the scaling, so whether you'd like to have them normal or fade or fill the space that they were given, so let's say fit, for instance. You can also configure whether you'd like to have a title or not and also you can set background colors and transparencies for your images in your reports. Great. So on to the next big feature area where we've made major improvements these months. We're talking a little bit about Data Modeling Improvements. The first improvement is actually a really significant milestone for us, in terms of data modeling is calculated tables. So what is a calculated table? A calculated table is actually like a calculated column, so it is calculated from other tables and columns that already existed in the model and it actually takes up space in the model just like the calculated column does. So why are calculating tables useful? They are a general purpose feature and they actually kind of hold any valid table DAX Expression. So, for instance, some of the common functions that you'll be able to apply by means of a calculated table would be things like doing a distinct or a cross join, a union, inner join, left outer join, intersections and even define calendar tables. So you can actually define those calculated tables with those DAX Expressions and they're gonna be recalculated when the model is reprocessed. And then, so the columns in the calculated tables, they all have data types, they have formatting and you can apply categories and default summarization, so they're just like any other table in the model except that they were calculated based off of DAX Expression. You can also, of course, define relationships between calculated tables and any other tables in your models. So, well, since calculated tables are a part of the model, they can actually be used, among other things, to simplify DAX formulas where you can use intermediate calculating tables. You can also use them as a way to speed up calculations where the calculations are already in the model. You can use them to create calendar tables or even to partition a model app with role-playing dimensions. So you can actually use a dimension as a way of partitioning a table into multiple tables by using calculated tables. So in addition to creating permanent model tables, calculated tables can also be used to help you with debugging DAX Expressions that return tables. So let's take a look at how to create calculated tables inside Power BI Desktop. I'm going to go into the Data View. And here we have the new table option. So for instance, here now that I have all the details and I have my order ID column, for instance, so we could define, for instance, order IDs could be the name of my table. And we could make it such that this is equals to distinct operation... of order details. Let's actually find the column name. So the other ID is going to be equal to this thing or... other details, other ID. So insert the reference to that. Sorry, having some hiccups here. Okay, finally insert it, sorry about that, and then once it is entered, this is actually going to recalculate the entire table and it's going to give me, in this case, it's a single column which has the distinct values for all of the order IDs. So you can see how we can start creating more complex DAX calculations on top of these calculated table and we can really use it to create any new table that we'd like to have based on any calculations that you could perform on top of your data model tables or different columns on different tables. So very powerful feature when you think about the power of the DAX Expressions that you can create and how you can combine them into many different ways to again, into new types of views or partitions for your data. Now, the next group of improvements that we've done in the modeling, data modeling area of the product this month is within the relationships view. This view is great for understanding the relationships across different tables in your data model. But up until now, it was actually mostly a view rather than a way of editing or modifying these relationships, which was possible through leave on buttons and so on but it was not actually possible within the diagram view. So now you can do three new things. The first one is create relationships by drag and dropping between two tables in the diagram. So, for instance, if I go here into the relationships view and actually let me demo first the second feature which is the ability to delete a relationship by clicking on-- hitting the delete key, I'm gonna be able to... Sorry, I'll delete a column but actually select the relationship and either right click to delete or hit the delete key, so now I'm going to delete the relationships and that was the second feature in the list. Now, once we delete the relationship, I'm actually going to show you how you can actually create a new one. So let's say that I want to create a relationship between the products table and the order details table because they both have a product ID. So I know that I can drag the product ID from here and drop it into the products table and it will automatically find the right columns to relate, in this case, by name, it's an easy one, product ID. But it also finds the right cardinality for me, so you see how it automatically set the relationship to be one to many between products, product ID and other product ID, so that's all great. The last new feature within the relationships view is the ability to rename and delete tables and columns which is kind of was, what was getting on the way for me on the previous one that I wanted to demo because I was selecting a column and I was trying to right click. So now you can actually delete a field from here or you can rename the field or you can hide it so that it actually doesn't show up in the report view in the fields list. So if you had internal fields that you wouldn't want to share with the report author and you would like to keep them only on the data model layer, this is a perfect and a great way of doing it within the relationships view. Great, let's go into the next group of features. Within the Data View, we've added the ability to copy contents from tables into the clipboard. So let's go back into the Data View and let's pick a more interesting table than this, so, for instance, let's pick other details. So now I can right click and I can select whether to copy the entire table or to copy a single column that's-- let's go and select it so if I copy the entire table and then go into Excel and paste, this copy of the entire table that I had in the view, of course. And, so as you can see, it actually contents lots of records or lots or rows, in this case, it will contain up to 2,155 which is exactly how many rows I had in the entire table. And you can also do this with the specific column so, for instance, if I copy this unit price column and I paste it somewhere else, over here, like, for instance, in column Y, I'm going to be able to just paste that one column, so full flexibility in terms of what you can copy out of the Data View and into Excel or into a text view or even things like Outlook or Word, they will all work great. We try to do the best job we can in pasting these tables into the right format for the destination program which you're pasting in, so we hope that this will be a really valuable feature for you. And the last group of Data Modeling Improvements is around field summarization. The first one is actually something that shows up in the Data View, sorry, in the report view, so it actually lets you define additional type of aggregations or summarizations within the fields pane. So we added support for median, standard deviation and variance. So let's go back to Power BI Desktop and if I go into the report view and I pick something like total sales by year, actually let's do something else, let's pick a different field here. Instead of total sales by year, I'm going to do the total number of units sold by year. So for that I can pick the quantity which the default aggregation is sum, as you can see that's the default for all of them. But now, I can go into this drop down and in addition to sum, average, min, max and count that we had before. We've added three new ones that, for now, they're non-default but you can actually customize that to be the default, so if you go back to the Data View, for instance, and you select that same field that I have here, on other details, that's the quantity. You can actually see now in the ribbon, how there is a new drop down for default summarization, so the default is sum, but I could turn that into the max. And now, if I go back to the report view... And I remove the sum of quantity which was defined before and I drag quantity again. That's going to default to the max, maximum, so as you can see, these are ways for you to start enriching your data model with additional information, so that the right default behaviors happen when you or someone else starts offering the report on top of that date model. Great. So let's go into the last two categories of improvements this month. The first one is data connectivity where we have ten different improvements. The first one is support for On-Premises Spark distributions. So last month, we added support for HDInsight Spark, and this month we're expanding that to other Spark distributions. So this new connector can be found in the get data dialog, under the other category. So let's go and take a look at it. We can go under get data, other and Spark will be next to last over here. And once you select it, of course it will ask you for the cluster that you're trying to access or the url and you can also select the protocol that you'd like to use to connect, whether standard or http. So after you click okay, you will get prompt for credentials. And then you will be able to navigate the list of tables available within the Spark server and you'd be able to load directly or edit them just as you would do with any other data servers. The second new feature this month is actually an improvement on top of the existing SharePoint list connector. It used to be such that the SharePoint list connector would allow users to import data from the SharePoint sites but there used to be a limitation that only sites where the site language was set to English would work with our connector. This month, we've improved the connector so that we've removed these restrictions, so now users can get data from any SharePoint list in any site, as long as it's SharePoint 2013 or newer, regardless of the site language. So for sites that are older, such as SharePoint 2010 sites, they continue to work just like they did before but they still expose the same language limitation because this improvement has actually been done only on newer versions of the SharePoint API. The next new feature on the data connectivity this month is an improvement to the exchange connector. So now, we actually allow connections to multiple mailboxes. Users can provide the email address to connect to a part of the source dialog and then they'll be able to specify credentials for that source, which you may actually use them to match the same email address that you're connecting to or not, so for those of you who actually have a mailbox that is being accessed from many different email addresses, you could actually configure that as well. So this way, you can actually start creating or combining data from multiple exchange mailboxes into a single report which was a very frequent request from many of you using the exchange connector. The next feature or improvement this month on the data connectivity is within the Excel Workbook connector. As you may already know, we did perform tight detection for columns imported from XLS files before. But we didn't do this detection for XLS files. So that's what we're having this month, we're having this automatic column type detection when you import XLS files through the Excel Workbook connector. The next feature is the ability to select related tables when you connect to a database source. So very frequently, users will import multiple tables when they connect to a database, but before this update, these users had to manually select all the tables that they wanted to input. So it often require understanding with the underlying databases schema would be so they would actually pick all the tables that are related. Now, with this update, we're having a new button called select related tables to the navigator dialog, so let's take a look at it. So I'm going to go connect to one my recent database servers, it's a SQL server database. And now, once we get into the navigator dialog, you will see, this new button shows up at the bottom, so it initially is disabled until you select at least one table. So, for instance, let's go and find my favorite database which, as many of you know, is Northwind. And now, we're going to go down to the tables areas, so, for instance, if I select something like products, and then I click selected related tables, it will select all of the tables that have a direct relationship with products, so in this case it picked up categories, order details and suppliers. Now, if I click it again, it will actually select all of the tables that have a direct relationship to any of these four tables, so that's going to give me a few more. In this case, it actually just gave me one more with orders, but now if I click it again, it's gonna pick up things like customers, for instance, and employees. So ultimately if I were to click these enough times, I would actually select the entire set of tables within my database as long as they have relationships but it actually helps once you pick one or two key tables that you know about and then you click select related tables so once you load this data into the model, you actually have all of the tables that you needed and they all have relationships automatically detect it for you and then it matches it for you to author your reports. And last thing I wanted to say about select related tables is that we've added support for this capability across many different databases that we support or database types including SQL Server, SQL Azure, Oracle, My SQL, it's actually easier if I show the list. So we added support for select related tables for SQL Server, SQL Azure, Oracle, DB2, MySQL, Sybase, and Teradata. Great. The next new feature or enhancement to an existing feature in this case is enhancements to the active directory connector. So you can actually use alternate credentials when you connect, so let's take a look at it. If I go into get data and I pick active directory. Let's find it on the list, active directory. The first thing that I'm going to be asked for is the domain that I would like to connect to. So in this case let's use something fake like my custom domain and click okay. Now, the credentials prompt would actually let me pick whether I'd like to use my current windows credentials or I would like to use alternate credentials with username and password for a domain account. So this will actually would make it much easier for me to impersonate as I write queries on top of active directory. The next new feature or improvement is, in this case, an improved experience when you connect to a data source and you actually import one or multiple functions, so as you do that now, you'll start being prompt automatically, right after you leave the navigator dialog where you picked those functions. Either, whether you click load or edit, we will actually ask you first for all of the parameters needed to evaluate those functions and then you will get the output of those functions into either the query editor or the data model and the report view, depending on the option that you picked. So this is much easier than it used to be where before we actually didn't prompt you and you had to go back into the query editor to pick one of these functions and then invoke it yourself and then load it again. So much, much more simplified experience for you to load functions and to invoke functions from data sources. The next one is an improvement on top of the import Excel Workbook Contents feature that we added last month which we knew was really successful and really popular amongst many of you that are completing hundreds and thousands of reports with lots of different types of report artifacts from Excel Workbooks into Power BI Desktop. So this month, we're actually enhancing that support to also support external connections to Analysis Services tabular models that were defined in Excel. So now this connections will get combined to AS live connections in Power BI Desktop. So they'll simplify a lot of work for you, so you don't need to recreate those reports manually. The last couple of options that we've added this month to data connectivity improvements are the ability to delete all of the entries in the data source settings dialog. So if you go into the data source settings dialog, so if you go through file, options and settings and go to data source settings, now, you're going to find next to the delete button, you're going to find the new dropdown with which contains the new delete all option, which will clear up all of the data source settings stored for me. So let's go back to the list. The last option is, the option to enable relationship import during refresh operations. So this one actually, this serves a little bit more of explanation. Basically, Power BI Desktop automatically creates relationships between tables when you load them, if these relationships existed in the data source. For instance, if you were connecting to a database as we saw before with the Northwind example, you'd be able to pick multiple tables with relationship and they will be automatically recreated for you in the local data modeling in your Power BI Desktop report. So in addition to creating this relationships during the initial log, Power BI Desktop might also create or delete relationships between these tables when you clicked refresh because if the relationship was gone from the database or it was added, we would actually refresh the schema as well as the data when you clicked refresh. So this used to be the default behavior and it was actually the only possible behavior before because there was no way to customize, to customize it. With this update, we're adding an option which is available on the-- under the options dialog so let's go take a look at it. It's on the-- under current file... it's under current file data load. So we have this new check box, update relationships when refreshing queries, which would basically let you configure the default behavior that you'd like to have. So the default, we've actually switched it to not create or not delete relationships on refresh, that's based on the feedback from some of you that actually find it better to actually lock down the schema, based on their first time load, results and then don't have the relationships coming or go away as you refresh your data. But if you like the old behavior, you can always turn this check box back on and it would recreate what you had in previews versions of the product. Great. So let's go into the last set of improvements this month. I know it's quite a long list but-- so these are just the last eight improvements. Those last few improvements all fall within the Data Transformations and Query Editor areas of the product. So the first one is copied to clipboard. We saw this bit earlier in this video for the Data View. We're also adding copy to clipboard capabilities on the Query Editor window. So let's take a look at that. We go into edit queries. And within the Query Editor, you're going to be able to copy contents from a cell, so if you right click on a cell, you're going to find copy, from a column, so you can find copy over there too. If you select multiple columns, you're also going to be able to copy or you could also do these from the table context menu, so you can copy the entire table. So let's do this, for example. Let's copy three different columns. I'll copy them to the clipboard. So now, keep in mind that missing the Query Editor window, you only have a preview or a subset of the data. In this case, it's giving me 999 rows, 999 plus because we actually give you 1,000 but we never look beyond that 1,000 so we don't know if there is more data or not. So now if you remember, I copied this exact same table before when I had them on the data, the Data View capabilities for the same, for copy to clipboard and we actually had something like 2,155 rows, I believe. 2155 was the number, right? So that's because we were copying the entire set of contents on that table because that's what we have within the Data View, we have the entire set of rows. So now if I paste what I copied from the Query Editor and I scroll down a little bit, you'll see how we only copy up to the first 1,000, in this case, because that's all we had. So as you can see, the data gets clipped at 1,000 because we only had those top 1,000 rows within the Query Editor preview so keep that in mind as you copy data. If you want to have entire data set, you'd copy it from the Data View which requires you to load the data first. If you have the smaller subset of data or you just wanted to copy something that was within the preview area in the Query Editor, you also have that capability. The second new feature within Data Transformations this month is the ability to filter date columns by the earliest or latest date and that's a dynamic filter. So let's go back to our Query Editor and let's go to the others table where they have a few date columns. So if I select older date and I go into date time filters, in addition to all of the other filters that we used to have, now, we also have is earliest and is latest. So if I actually select is latest, it's going to apply filter where first, it will calculate the latest date within the current set, and then it's going to apply that filter on top of the data. So as I said, this is actually a dynamic filter so if I refresh it again, next year, and we've got new rows of data with newer older date, it would actually filter by the latest date that it finds at that point. All right. So the next feature similarly to earliest and latest dates for filtering. We've also added transformation that would actually extract the earliest or latest value from a date or date time column which is available from the transform tab also here in the editor. So let me actually remove the filter step... and show you again, these are stated in the table how you can select that one column and then go into the transform tab and now if you go within the date menu or the time menu, you're gonna find these new options for earliest and latest. So if I select latest now, this will return as single scalar value that actually is the latest date that was found within this column. Great. The next transformation that we've improved this month is replace values. We've added the ability to specify special characters within the replace values dialog so you can use them to either find values or replace values. So let's go and find one of the columns like customer ID that's a text column, so if you go now into the replace values dialog within the ribbon, you're going to get a few advanced options to apply. So there's value to find and value to replace with, that's always been there. And then if you go to advance options, match entire cell contents used to be there as well, but now we've added replace using a special character, so if you use that option and you'd like to insert a special character, you'll actually insert that character in one of the two fields above that contains that has a focus at that point, so if I switch to replace with now and I click carriage return and line feed, you see that it actually inserts those two special characters. So this was possible before through custom editing and specifying the actually escape sequence for that special character but that was not very discoverable and not easy to know for you which one you actually had to use, so this is much simple now where you can just point and click to insert the right escape sequence for replacing values. Great. On to the last four improvements. So the first one is the ability to detect column types. Any specific point in time that you'd like to do within your query. So as you know, Power BI Desktop applies automatic column type detection when you import data from certain data sources like text files, web pages and so on, CSV files and so on. So basically, any data source that does not have any schema or data type information, we would actually compensate and try to detect the best column type for you, so that's a one time detection that we perform right as you import data from that data source for the first time, but there's no way for you or it didn't used to be a way for you to apply this end column type detection at any given table that you have within the Query Editor and so if you actually wanted to set the type for many different columns, you actually had to go and manually select them and specify the type that you wanted. So now we've improved that and we've given you the ability to detect data types. So, for instance, if I select these three columns, I have detect data type now under the transform top that when I click it, it will actually look at the rows of data that we have and it would detect automatically the type that it was better suited for these columns, so in this case, it actually detected a couple of int64 type columns or integrate columns, number columns and one of type text. So these will make it much easier for you because it will save you lots of time, so you can just select a bunch of different columns and then click detect types rather than having to go one by one and manually set the types for you. Great. The last or the next to last item on the list is the ability to refresh all previews, to refresh all the Query Editor previews with the single click. So as you know, as you may already know, we have refresh preview that would refresh a single preview for one of your queries, the one that's currently in focus, so as you can see now just refresh but if I wanted to refresh all of them, I would have to switch between them and click refresh one by one. Now, I can just go into this drop down under refresh preview and I have this new refresh all option that will go ahead and refresh all of the Query previews for me within all the tables that I'm having here. So again, one of those things that simplifies and makes it much more efficient for you to work with the Query Editor environment. If you knew you had to refresh all of them, that's much easier for you to do than going one by one. Great. And the last group of new features, well, actually they are performance improvements, those are always nice to have and as you know, we continue making performance improvements across many different areas of the product with a renew update. This month is the turn for improving the performance in these two areas. The first one is the choose columns dialog. So if I go into the choose columns dialog from a very wide table like, for instance, my customers' table, I believe, actually products table has more columns, so let's go into the products table that has 13 different columns. I can go into choose columns and the performance on bringing up the list within the choose columns dialog is immediate, as you saw, there was no noticeable lag in bringing up this list, and it's actually much more evident as you go with tables that have up to hundreds of columns, where you can get back into this view and immediately start selecting the columns that you'd like to keep without having to wait for that preview. Similarly to this, we've also improved the performance on the list of values to filter by, so as you saw, there was a little bit of loading time but it was just a few milliseconds and now we're able to more efficiently load the items within the filter menu as well as the expand and aggregate menu, so for instance, if I go to a table like other details that has this column with nested tables and nested records on them. You saw how the load of this menu was immediate. So that's-- basically the performance improvement we've done this month, it makes it much more fluent for you and much more efficient for you to work with your data as you apply filters and aggregates and expand operations. Great. So that's all for this month. I hope you guys enjoyed these new features. I hope you find them very useful. As I was saying, this is very massive improvement for us, very massive update with 44 new features and we're actually already working very hard on the next round of improvements. So we think that October will be also another great month for Power BI Desktop, so please stay tuned for future updates and in the mean time, keep using our product, keep sending us feedback and we really pay lots of attention to your feedback. We are committed to getting back to everyone of you with any questions or issues or suggestions that you may have and we look forward to hearing back from you. Thank you so much for your attention. Have a good one.

Video Details

Duration: 54 minutes and 48 seconds
Country: Andorra
Language: English
License: Dotsub - Standard License
Genre: None
Views: 1
Posted by: csintl on Sep 25, 2015

The September 2015 Power BI Desktop update is here loaded with 44 new features for report authoring, data modeling, connectivity and transformation. Check it out!

Learn more at https://powerbi.microsoft.com/desktop

Caption and Translate

    Sign In/Register for Dotsub to translate this video.