Modifying Columns, Functions, and Queries in Power BI

Name: Modifying Columns, Functions, and Queries in Power BI
Author: Elizabeth Vergara

In this chapter you’ll learn advanced techniques for transforming data, such as using the Advanced Editor. In addition, you’ll learn how to create parameters…

Preview the first lesson free — get full access to all 6 lessons.

Request a demo Sign in

Course: On-Demand

In this video, we will review the advanced editor, let's get started. The advanced editor lets you see code that power query editors creating with each step. It also lets you create your own shaping code, to launch the advanced editor, we'll go into home, click on transform data which will open up the power query editor, and from here we'll go to the home tab and click on advanced editor. A window will appear showing us the existing query code. We can see how the source was imported, which connected to Power BI, as well as the data types which were defined. The advanced editor is also helpful when troubleshooting transformation code. We can notice how each subsequent line of code references the previous line of code. For example, if we look at this line of code change type we can see that there is a function to transform this tab, but we referenced the previous line of code which has promoted headers which is this second line of code. Say we wanted to add a custom column which displays a customer's full name, their full name including their first name and their last name. What we would do is we would paste in our code which is added custom. So we're gonna remove this piece of code and replace it with added custom, is what we'll call this transformation step. And then we use the function which is table.addcolumn. And we referenced the previous transformation step which is called change type. And we name our column, the customer full name, And for each record we're going to take the existing first name and concatenate that with a space and use the existing last name. In addition, what we'll do here is scroll all the way over to the right, and for the existing steps, we're gonna have to add a comma since we're adding a subsequent step. this will ensure that our M-code will function properly and give us no errors. And when we're done, we'll finally place that added custom as our last step referring to the last step within the transformation. And then we'll click down. If we scroll all the way over to the right of our customer table, we'll see that the new column is now present which has the customer's first name concatenated with their last name, with a space in between. This concludes our session on the advanced editor. Thank you for watching.

Intermediate Provider : Elizabeth Vergara 6 Lessons · 17m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

In this chapter you’ll learn advanced techniques for transforming data, such as using the Advanced Editor. In addition, you’ll learn how to create parameters to dynamically filter datasets, create columns from examples, as well as configure custom functions and conditional columns based on if, then, and else conditions. Finally, you’ll learn query dependencies, which will help in understanding sources and targets for datasets and queries.

What You'll Learn

Use the Advanced Editor to apply advanced data transformation techniques
Create parameters to dynamically filter datasets
Build columns from examples and configure conditional columns based on if, then, and else conditions
Configure custom functions in Power BI queries
Trace query dependencies to understand sources and targets for datasets and queries
Model data using advanced techniques including many-to-many and bidirectional relationships

Key Takeaways

The chapter covers advanced techniques for transforming data, such as using the Advanced Editor.
Parameters can be created to dynamically filter datasets in Power BI.
Conditional columns are configured based on if, then, and else conditions.
Query dependencies help in understanding the sources and targets for datasets and queries.
The course covers advanced modeling topics including many-to-many and bidirectional relationships, row level security, and aggregations.

Frequently Asked Questions

What does this Power BI chapter cover?

It covers advanced techniques for transforming data using the Advanced Editor, creating parameters to dynamically filter datasets, creating columns from examples, configuring custom functions and conditional columns based on if, then, and else conditions, and understanding query dependencies for the sources and targets of datasets and queries.

What lessons are included in this course?

The lessons are Advanced Editor, Parameters, Column from Examples, Custom Functions, Conditional Column, and Query Dependencies.

What skills will I gain from this course?

The course develops skills in Advanced Analytics, Data Manipulation, Data Transformation, Microsoft Power BI Embedded, Power BI, and Query Designer.

What advanced modeling topics are addressed?

The learning objectives include modeling data using advanced techniques, many-to-many and bidirectional relationships, migrating an Excel data model and report to Power BI, advanced properties, row level security, and aggregations.