The Power BI Next Step conference will host 3 concurrent full-day training sessions on Thursday, September 12th.
Deep Dive into Power Query and Data Transformation
A data analytics solution requires multiple layers of development, from accessing the data, preparing it, modeling it, applying analytical business logical calculations to it, and finally, visualizing it. Data transformation and data preparation are usually the most time-consuming parts of any BI solution. It is very common to spend more than 70% of the total analytical project time on data preparation and data transformation. The right data transformation can lead to a powerful model; however, a bad data transformation won’t last long. Data transformation and preparation is one of the foundations of any analytical system.
Power Query is the Data transformation engine in Power BI. Power Query enables developers not only to connect to the data source but also to transform the data before loading it into Power BI. Power Query leverages a rich graphical interface called Power Query Editor, however, there is a functional language behind the scene (called M) which is even much more powerful. To become a proficient Power BI data analyst, you will need to become a professional in data transformation using Power Query.
This workshop will start with a very quick overview of Power Query and data transformation but then dive deep into advanced topics of data transformations such as parameters and custom functions, the M language, etc. It will also have a module on what dataflow is, how you can use it in your Power BI Solution, how it can level up your Power BI solution to a better solution, How it can be implemented, tips and tricks for moving from Power Query to dataflows, and some advanced transformations in the dataflows that helps your solution and data model.
Topics covered:
- Power Query 101: a very brief introduction
- Combining queries: Merge and Append tips and tricks
- Table and Structured column transformations
- Functions and Parameters: Dynamic Power Query
- Power Query M Language
- Data Structures in M and important functions related to them.
- Advanced M Scripting
- Error handling and troubleshooting
- Dataflows and Power Query Editor Online
This workshop is recommended for everyone who uses Power BI and, of course, uses Power Query for data transformation and load in Power BI. Reza Rad, Author of Power BI, Power Query and Dataflow articles in Microsoft documentation and author of 13 books on Power BI, shares his experience and tips and tricks about Power Query.
Reza Rad
Author, Trainer, Speaker and Consultant
Using Deneb to Create Highly Bespoke Visuals
Business Intelligence projects are complex operations, and a large contributor to their success is the solid foundation of architecture, semantic models, and clean and repeatable data transformations. These can be engineering marvels, but your report consumers only sometimes appreciate the effort that this involves, and it can be challenging to show progress at these stages. The visual layer of reports can often suffer due to time pressures in getting the semantic model right, but this gives these consumers a window to your hard work. As such, it is essential that you have a similar degree of control over the visual canvas so that you can make sure that your users are engaged and correctly informed.
When constructing the perfect report, you have many options, starting with the core visuals and, if your organization permits it, a wealth of custom visuals developed and maintained by third parties. However, you can be somewhat limited if you have a visual that doesn’t meet your requirements. Microsoft provides the means to develop custom visuals yourself, but this has a steep learning curve and requires report developers to be familiar with web development technologies (HTML/JavaScript/CSS), which is not the cross-skilling you can accommodate on-demand if you don’t already have the experience. You also can leverage R and Python visuals, but these have some considerations and limitations regarding deployment.
Deneb, a free and open-source custom visual for Power BI, offers a unique ‘middle-ground’ between off-the-shelf visuals and full-blown development options. It leverages the Vega and Vega-Lite languages developed and maintained by the University of Washington Interactive Data Lab. These languages use a declarative visualization grammar concept, allowing you to build bespoke visuals using JSON without complex coding. Deneb also provides access to other Power BI functionalities, such as tooltips, cross-filtering, and formatting. It’s been available in AppSource for almost three years and is certified by Microsoft, ensuring your designs work seamlessly in PDF & PowerPoint exports, publish to web, and mobile reports. The community around Deneb and the Vega languages is steadily growing, and it is fast becoming a go-to tool for bespoke visualization requirements in Power BI. Daniel Marsh-Patrick is the creator and maintainer of Deneb and has considerable experience developing custom visual solutions in Power BI.
This workshop is not just about learning a new tool, but about gaining a new perspective on visualizing data. It’s recommended for users who wish to gain more control over the visual layer in Power BI. You will learn to use Deneb and Vega-Lite to “think visually” when ideating and iterating a bespoke solution rather than choosing a pre-defined visual from a palette. This includes thinking about interactivity features so that our users can take action from insight, what we have to consider in terms of designing these features, and Power BI’s limitations. You will also learn about the process of templating your work so that you can reuse it elsewhere in your organization or share it with others. With these principles established, the workshop will conclude with an exploration and explanation of some more advanced examples available and the key differences between Vega-Lite and Vega so that you finish up armed with the concepts to research and develop your own visual solutions.
To get the most out of the session, you should bring your laptop and charger so that you can follow along and experiment with your own ideas and variations to the exercises. You will need to have Power BI Desktop installed, and you should either have access to AppSource visuals or access to Deneb as an organizational visual for your tenant.
Daniel Marsh-Patrick
Author of Deneb, Founder & Principal Consultant at Coacervo
Advanced Semantic Model development with Tabular Editor
Power BI Desktop is a great tool for learning the basics of Semantic Model development. However, if you really want to get your hands dirty and unlock the full potential of Semantic Models in Power BI and Fabric, you’ll need a tool that goes beyond the surface. That tool is Tabular Editor.
In this full-day workshop, we’re going to take the gloves off and dive deep into the Tabular Object Model (TOM), to gain a complete understanding of the metadata structure behind every Analysis Services Tabular Model and Power BI / Fabric Semantic Model. We’re going to explore objects and properties that you might not even know existed! Tabular Editor (free or paid version) is our tool of choice in this endeavour, as the tool gives us full, unlimited and unfiltered access to the TOM tree behind the model. Moreover, Tabular Editor lets us interact with the TOM programmatically through C# scripts – a super powerful feature, when you’re looking to automate certain operations or workflows on your Semantic Models.
Join this workshop if you’re curious about taking your Semantic Model development skills to the next step.
Topics covered:
- Introduction to AMO and TOM
- TOM exploration using Tabular Editor and C# scripts
- TOM exploration using PowerShell and other tools
- Understanding TMSL (Tabular Model Scripting Language)
- Advanced TOM objects and properties
- Best Practice Analyzer and custom rules
- Automation with the Tabular Editor CLI
- Use cases and scenarios
Learning Objectives:
At the end of this training day, attendees will have gained the following:
- A solid understanding of the Tabular Object Model (TOM), and advanced objects/properties within it.
- Practical skills on how to work with the TOM, using Tabular Editor, C#, PowerShell and other techniques.
- Tips and tricks that make you more productive when developing Semantic Models
- Knowledge about automation, enabling advanced management scenarios for DevOps and Deployment
Prerequisites:
The training assumes that you are familiar with basic Semantic Model concepts: Tables, columns, measures, relationships, and know how to navigate these basic objects using Tabular Editor (free or paid version). For hands-on exercises, make sure to have Tabular Editor pre-installed on your machine.
Daniel Otykier
Author of Tabular Editor, Microsoft Data Platform MVP