Tag: Data Preparation

Business Intelligence - BI, Dataflows, Excel, Power BI, Power BI Desktop, Power BI Desktop RS, Power BI Service, Power Query, Self-Service BI

Quick Tips: Renaming All Tables’ Columns in One Go in Power Query

Renaming All Tables' Columns in One Go in Power Query

I previously wrote a blog post explaining how to rename all columns in a table in one go with Power Query. One of my visitors raised a question in the comments about the possibility to rename all columns from all tables in one go. Interestingly enough, one of my customers had a similar requirement. So I thought it is good to write a Quick Tip explaining how to meet the requirement.

The Problem

You are connecting to the data sources from Power BI Desktop (or Excel or Data Flows). The columns of the source tables are not user friendly, so you require to rename all columns. You already know how to rename all columns of a table in one go but you’d like to apply the renaming columns patterns to all tables.

The Solution

The solution is quite simple. We require to connect to the source, but we do not navigate to any tables straight away. In my case, my source table is an on-premises SQL Server. So I connect to the SQL Server instance using the Sql.Database(Server, DB) function in Power Query where the Server and the DB are query parameters. Read more about query parameters here. The results would like the following image:

The Results of Sql.Database() Function in Power Query
The results of running the Sql.Database(Server, DB) function

As you see in the above image, the results include Tables, Views and Functions. We are not interested in Functions therefore we just filter them out. The following image shows the results after applying the filter:

Filtering out SQL Server Functions After Connecting from Power Query
Filtering out SQL Server Functions

If we look closer to the Data column, we see that the column is indeed a Structured Column. The structured values of the Data column are Table values. If we click on a cell (not on the Table value of the cell), we can see the actual underlying data, as shown in the following image:

Continue reading “Quick Tips: Renaming All Tables’ Columns in One Go in Power Query” →
Business Intelligence - BI, Power BI, Power Query, Quick Tips

Quick Tips: Conditionally Replace Values Based on Other Values in Power Query

Power Query (M) made a lot of data transformation activities much easier and value replacement is one of them. You can easily right click on any desired value in Power Query, either in Excel or Power BI, or other components of Power Platform in general, and simply replace that value with any desired alternative. Replacing values based on certain conditions however, may not seem that easy at first. I’ve seen a lot of Power Query (M) developers adding new columns to accomplish that. But adding a new column is not always a good idea, especially when you can do it in a simple single step in Power Query. In this post I show you a quick and easy way to that can help you handling many different value replacement scenarios.

Imagine you have a table like below and you have a requirement to replace the values column [B] with the values of column [C] if the [A] = [B].

Sample Data in Power BI

One way is to add a new conditional column and with the following logic:

if [B] = [A] then [C] else [B]

Well, it works perfectly fine, but wait, you’re adding a new column right? Wouldn’t it be better to handle the above simple scenario without adding a new column? If your answer is yes then continue reading.

Continue reading “Quick Tips: Conditionally Replace Values Based on Other Values in Power Query” →
Business Intelligence - BI, Excel, Power BI, Power BI Desktop, Power Query, Quick Tips, Self-Service BI, T-SQL

Quick Tips: How to Filter a Column by another Column from a Different Query in Power Query

Filter a Column by a Column from a Different Query in Power Query

A while ago I was visiting a customer that asked if they can filter a query data by a column from another query in Power BI. And I said of course you can. In this post I explain how that can be achieved in Power Query. The key point is to know how to reference a query and how to reference a column of that query in Power Query. This is useful when you have a lookup table that can be sourced from every supported data source in Power Query and you want to filter the results of another query by relevant column in the lookup query. In that case, you’ll have a sort of dynamic filtering. So, whenever you refresh your model if new records have been changed in or added to the source of the lookup query, your table will automatically include the new values in the filter step in Power Query.

Referencing a Query

It is quite simple, you just need to use the name of the query. If the query name contains special characters like space, then you need to wrap it with number sign and double quotes like #”QUERY_NAME”. So, if I want to reference another query, in a new blank query, then the Power Query (M) scripts would look like below:

let
    Source = Product
in
    Source

Or something like 

let
    Source = #"Product Category"
in
    Source

Referencing a Column

Referencing a column is also quite simple. When you reference a column you need to mention the referencing query name, explained above, along with the column name in brackets. So, the format will look like #”QUERY_NAME”[COLUMN_NAME]. The result is a list of values of that particular column.

let
    Source = #"Product Category"[Product Category Name]
in
    Source
Referencing a Column from Another Query in Power Query
Continue reading “Quick Tips: How to Filter a Column by another Column from a Different Query in Power Query” →