The Summit kicks off Sunday! Looking over the session catalog there are fantastic options in every timeslot, but some of the sessions I’m most anticipating are the ones focusing on the data transformation abilities built in to Power BI and the native Power Query.
It’s certainly possible to spend all sorts of time in Power BI without exploring what this part of the tool can do for you. But once you dive in, this tool provides capabilities that aren’t (or at least haven’t previously been) included with anything other platform.
We are in the midst of an evolution in our industry, from the ETL model (Extract, Transform, Load) to a more open-ended ELT (Extract, Load, Transform) approach. As the tools have improved, so have our methodologies. And Power BI, built on top of Power Query, was built from the ground up to facilitate the ELT approach.
In the ELT approach, data is extracted, and loaded in its native state. Transformation steps are applied afterwards as necessary to enable successful reporting.
Power Query provides extensive transformation tools on datasets it can ingest: Pivoting/Unpivoting columns, replacing values, calculating derived columns, and filtering/summarizing rows are all supported via an interface that provides a procedural, stepwise approach to delivering consistent results across any refresh to your data.
This approach continues throughout the entire tool. Beyond rule-based transformation, PowerBI can also calculate new tables on the fly from source tables based on any logic that can be applied to aggregated data.
This is enormously powerful. As a simple example, we’ve just delivered a proof of concept for a prospective client. Data was provided in three unrelated tables – spend, savings vs goal, and performance. Existing excel-based reporting required manual filtering of the data to align these three sets of data.
After completing initial discovery and replication of the existing reporting, we identified substantial overlap between dimensions on these three fact tables. With only a minimal amount of transformation, we were able to align values for Vertical Market, Division, Country, and Client.
However, PowerBI does not allow for many-many joins in the client-side engine. We still needed to construct dimension tables to link these separate facts. Prior workflows would have required constructing these via separate queries and loading these separately. However, we were able to perform this dynamically by calculating the dimension table at runtime with the following logic:
Click to Copy
This logic results in a table with a single column containing each distinct non-null value for the dimension from all three source tables. Relationships can then be set between the new dimension table and each of the three data tables, and the data grouped and filtered by the new dimension.
Applying this method to all four shared dimensions, we were able to dynamically report aligning data from all three data tables in a way that had not previously been simple for the client. That we were able to deliver this quickly without manual data transformation was a key differentiator in the success of the PoC.
The takeaway being: As you finalize your schedule for the Summit, give strong consideration to the sessions concerning Power Query, M (Power Query’s native query language), and DAX! PowerBI has so much going for it, but these capabilities can take the tool (and your reporting) to the next level.
We look forward to seeing you in these sessions!
Data in the real world is messy and rarely available in the exact form that users need for their analysis or reports. Data analysts need to spend a significant amount of their time working on data preparation before the data is ready to be visualized. Microsoft Power Query and M integration in Power BI Desktop provide the best-in-market experience for importing, reshaping and combining data from a wide range of data sources. In this session, we will teach you everything you need to know to start leveraging these capabilities and take your reports to the next level. We will also show you several demos about new capabilities coming to Power Query over the next few months.
Preparing and defining ETL for insights is a significant challenge for businesses today—ingestion, cleansing, transformation, and enrichment are labor-intensive and time-consuming tasks, which require deep technical skills. Microsoft Power BI now introduces advanced data prep with dataflows—a suite self-service, low-code/no-code feature and capability for business analysts to easily process, unify, and store their data in Microsoft Azure based data-lake storage. With these new capabilities, Power BI offers a solution for any business need, whether you want to prep your data with ease, using a familiar built-in Microsoft Power Query experience, or to leverage the full Azure stack for more advanced use cases. Join this workshop to learn how to easily prep your data, leverage Microsoft’s standardized schema, improve time-to-value, eliminate data silos, and create one source of truth for your organizational insights. IMPORTANT – Please note: This workshop has a capacity of ~100 attendees. Attendance is on a first come first serve basis. Adding this workshop to your calendar does not guarantee you a seat.
The M language is the language that Microsoft Power Query uses inside the Get Data experience of Excel and the Power BI Desktop. In this session we’ll take a deep dive through the M language to cover things like values, syntax, tokens, row context and more.
Preparing and defining ETL for insights is significant challenge for businesses today—ingestion, cleansing, transformation, and enrichment are labor-intensive and time-consuming tasks, which require deep technical skills. Power BI now introduces advanced data prep with dataflows—a suite self-service low-code/no-code features and capabilities for business analysts to easily process and unify their data and store it in Azure-based data-lake storage. With these new capabilities, Power BI offers a solution for any business need—whether you want to prep your data with ease, using a familiar built-in Power Query experience, or to leverage the full Azure stack for more advanced use-cases. Join this session to learn how to easily prep your data, leverage Microsoft’s standardized schema, improve time-to-value, eliminate data silos, and create one source of truth for your organizational insights
Microsoft Power Query is a fabulous tool for cleansing and loading data into Power BI, Power Pivot, and Excel. The UI lets you do almost everything you can imagine just by clicking on the menu options. Yet under the hood of Power Query is a very powerful functional language called “M”. There is currently no IntelliSense supporting the M language in the UI, but you can learn a lot about how the language works. In this session, be introduced to the basics of the M language and demonstrate concepts on how to learn more as you write queries. IMPORTANT – Please note: This theater session is located in one of four Microsoft product theaters located on the Expo Floor with seating for roughly 20 attendees in addition to standing room. Sessions are 20 minutes and run every 30 minutes in each theater. Viewing availability is on a first come first serve basis. Adding this theater session to your calendar does not guarantee you a seat.
Microsoft Power Query is the transformation engine in Power BI. This is the engine that you do all data preparation before loading data into the model. This is the keystone of your Power BI solution. In this session, you will learn what Power Query can do. You will learn how M, the language behind the scene of Power Query, can be more beneficial than the Power Query graphical interface. You will see demos of transformations that you can do with M Code. You will learn about functions, parameters, generators and many other amazing features of this tool. Prepare to be amazed with what Power Query can do in this demo filled session!
We live in a world of choices and we have many tools at our disposal. In Microsoft Business Intelligence solutions using tools like Power BI and SQL Server Analysis Services, you have at least three different ways to perform data collection, transformations and calculations. A question I get all the time is: “Which database or BI tool should be used to perform routine tasks? Is it best to shape and transform data at the source, in Power Query using M script, or in the data model using DAX?”In this theater session Paul Turley will demonstrate options for creating utility and dimension tables, columns and calculations using each option and discuss the advantages, disadvantages and recommended practice for each. IMPORTANT – Please note: This theater session is located in one of four Microsoft product theaters located on the Expo Floor with seating for roughly 20 attendees in addition to standing room. Sessions are 20 minutes and run every 30 minutes in each theater. Viewing availability is on a first come first serve basis. Adding this theater session to your calendar does not guarantee you a seat.