Power Query Updates in Power BI Desktop April 2021 Release
Power BI DesktopPower Query Online
The Power BI Desktop April 2021 Release contains many Power Query-related updates, summarized below for easy reference. To view the full list of feature releases in Power BI Desktop April 2021, read the full Power BI blog post here.
New Connector: Bloomberg Data and Analytics
We are excited to announce the release of the Bloomberg Data and Analytics Power Query connector in Power BI Desktop. Here is more about the connector from Bloomberg’s team:
“We are happy to announce Bloomberg’s Data and Analytics connector this month! Bloomberg empowers capital markets professionals and their firms with the tools and data needed to successfully implement optimal investment portfolio strategies and to make quicker, more informed decisions. The Bloomberg Data and Analytics connector for Power BI brings Bloomberg’s best-in-class portfolio analytics to Power BI through Bloomberg’s PORT Enterprise reporting solution. Portfolio ex-ante risk, sophisticated performance attribution, and portfolio characteristics data (both absolute and relative to the benchmark) can be accessed via Power BI. Data can be accessed at the portfolio, security or group level, for one or more portfolios, either for a particular date or as a time-series.”
New Connector: SoftOne BI
We are also excited to announce the release of the SoftOne BI Power Query connector in Power BI Desktop. Here is a summary from the SoftOne BI team:
“SoftOne BI is a comprehensive business intelligence service that enables Soft1 ERP and Atlantis ERP users to explore and analyze data more efficiently and make faster, more informed decisions, with insightful information.
SoftOne BI provides access to Soft1 ERP and Atlantis ERP data in a standardized, optimized format for reporting and analytics of several templatized scenarios such as sales, financial and CRM or completely custom scenarios. The SoftOne BI connector imports data from an Azure Data Lake (Gen 2) for both on-premises and Software as a Service (SaaS) installations, that has been uploaded via an ETL process from each customer’s ERP or CRM.
Combining SoftOne’s built-in powerful analytics, advanced predictive models, and machine learning technologies with the industry leading capabilities of Power BI, raw data is instantly transformed into easy-to-understand visualizations and real-time metrics across every department, subsidiary and location, powering up decision-making across any organization.”
Text/CSV By Example now generally available
We’re pleased to announce that “Text/CSV By Example” in Power Query is now Generally Available. This feature makes it extremely easy for users to extract data from Text or CSV files without having to think about the data transformations to apply, rather just providing a set of sample values to extract and let Power Query infer the transformations to achieve it.
When using the Text/CSV connector, users will see a new option to “Extract Table Using Examples” on the bottom-left corner of the file preview dialog.
Upon clicking that new button, users will be taken into the “Extract Table Using Examples” experience allowing them to specify sample output values for the data they would like to extract from their Text/CSV file.
Once users are done constructing that table, they can click Load/Transform to complete the Get Data flow. Notice how the resulting queries contain a detailed breakdown of all the steps that were inferred for the data extraction, which are just regular query steps that can be customized as needed.
Automatic Table Detection from Excel files now generally available
Excel is one of the most commonly used sources of data for reporting and analytics. Many will also say that Excel is the most popular database in the world!
A typical challenge with extracting data from Excel files is that the data very often is not formatted as tables so the task to “scrape” relevant data from an Excel spreadsheet can be extremely challenging and time consuming for users.
For the past 8 months, we have iterated on Preview feedback for our automatic table detection on top of Excel files and we are now extremely happy to declare this feature as Generally Available!
When using the Excel connector, this feature will automatically identify sections of each Excel spreadsheet to extract into a table and show them under a “Suggested Tables” group in the Navigator.
Previously, unless data was formatted as Tables or Named Ranges in Excel, users had to scrape the relevant rows/columns with specific transforms (skip rows, remove columns, etc.) from worksheet objects (e.g. Sheet1 in the example below).
Automatic Table Detection from JSON files now generally available
Similar to the previous feature for Excel automatic table detection, importing data from JSON files (or Web APIs) can be equally challenging for end users. Here is an example of JSON file with multiple levels of nested data.
With the addition of Automatic Table Detection capabilities, when using the JSON connector Power Query will automatically apply transformation steps to flatten the JSON data into a table. Previously users had to flatten records/lists manually. This new feature also adds support for JSON lines (or newline-delimited JSON, where each line in the file is a JSON string).
Feedback
The Power Query team is interested in your feedback! Feel free to leave a comment below with your thoughts.