Today we’re announcing a new update to the Power BI Designer Preview.
This month’s update is packed with lots of new features across the Data Modeling, Analytics, Get Data & Transformations areas of the product:
Data Modeling & Analytics
- Calculated Columns
- Data Categorization
- Sort by Another Column
- Improved DAX Formula Editor: Function Help and Prototype
Get Data & Transformations
- New “ODBC Tables” connector (Beta)
- Improvements to the “Excel Workbook” connector:
- Better Column type inference
- Faster load for data previews
- New Text Column Filters – “Does Not Begin With” & “Does Not End With”
- Enhanced Privacy Levels dialog
You can continue reading below for more details about each item.
Calculated Columns
This month we’re adding the ability to create “Calculated Columns” from the Report view.
With Calculated Columns, you can add new data to a table already in your model. But instead of querying and loading values into your new column from a data source, you create a Data Analysis Expressions (DAX) formula that defines the column’s values.
You can create “Calculated Columns” from the Report view by using the “New Column” button under the “Data Tools – Modeling” contextual tab.
Calculated columns you create appear in the Fields list just like any other field, but they’ll have a special icon showing its values are the result of a formula. You can name your columns whatever you want, and add them to a report visualization just like other fields.
You can watch this feature in action in the following video:
Learn more about Calculated Columns in the Power BI Designer
Data Categorization
Another new feature this month is the ability to specify data categorization for columns loaded into your report. When the Power BI Designer imports data it gathers all the information it can from the source (e.g. it is a primary key), the data in the column, the table and column names, etc. With that information the Power BI designer makes some assumptions about how to give you a good default experience when creating a visual. A simple example is if we know that a column is a date time column we’ll assume a time hierarchy for an axis on a line chart. A more difficult example is geography.
With this month’s update, you can manually specify the categories for your columns by accessing the Data Category dropdown menu in the “Data Tools – Modeling” tab within the Report view.
Watch the following video to learn how to use this feature in the Power BI Designer.
Learn more about Data Categorization in the Power BI Designer.
Sort By Another Column
With Sort by Column, on the Data Tools Modeling tab in Power BI Designer, you can change how values in a column are sorted in a visualization.
When you add a column to a visualization, the default sort order works well most of the time. But, sometimes a column’s natural sort order isn’t really what you need.
Common cases where you would want to use “Sort By” column include: Weekdays or Months (sort days/months by chronological order rather than by alphabetical order), Funnel Stages (sort by logical order rather than alphabetical), etc.
“Sort By Column” can be found under the “Data Tools – Modeling” tab within the Report view.
This option brings up the sorting dialog where you can specify which column you want to sort and which column will define the sorting order.
You can watch the following video for more details.
Learn more about “Sort by Another Column” in the Power BI Designer
Improved DAX Formula Editor: Function Help and Prototype
We’re improving our DAX formula authoring support by offering you function help and prototype tooltips as you author your formulas.
New “ODBC Tables” connector (Beta)
One of the biggest challenges when trying to connect to a data source via ODBC is having to write custom query statements to specify the data that needs to be retrieved.
With this month’s update, we’re making it possible for users to retrieve tables via ODBC without having to provide a query. Simply specify the Connection String and use the Navigator dialog to select one or multiple tables. You can also reshape these tables or mash them up with other tables in the Query view, just like you would do when connecting to other sources.
Learn more about this feature by watching the following video:
Improvements to the “Excel Workbook” connector
We have improved the “Excel Workbook” connector in two different areas this month:
- Improved Column Type Inference when importing worksheets.
- Faster load for Data Previews.
New Text Column Filters
In this update we have added a couple of new Text Filters to the Query view: “Does Not Start With” and “Does Not End With”. These filters are available within the Filters menu for Text columns.
Watch the following video to see these new filters in action:
Enhanced Privacy Levels dialog
We are improving the Privacy Levels dialog where users are asked to provide privacy levels for all data sources involved in a query. With this update, users can control whether privacy levels apply to a specific location or a more general one. For instance, control whether privacy levels should be applied to a page vs. an entire site.
See this feature in action:
That’s all for this month. As mentioned previously, we’re making lots of incremental improvements to the Power BI Designer and we hope that you find it better with every new monthly update…
Please continue sending us feedback using our “Send a Smile/Frown” feature, or by voting for what you’d like to see next.
Here is also a full version of the video that combines all What’s New videos from this announcement.