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
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?
- New Transfomation: 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.
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.
- Try Power BI
- See Power BI in action
- Download Power Query
- Follow @MSPowerBI on Twitter to learn more about how you can #ExpressUrCells!