You can see as an example how the conditional column script in M translated to Case statement in T-SQL.

#'Added Conditional Column' = Table.AddColumn(Sales_SalesOrderHeader, 'Custom', each if >= 100 then '0' else '1' )ДЄnd here is the folded version of that translated to native T-SQL query: select. In other words when you run Power Query script on top of a SQL Server database, query folding will translate the M script into T-SQL statements, and fetch the final results.ДЄdventureWorks2012 = Source, Query Folding means translating Power Query (M) transformations into native query language of the data source (for example T-SQL). I canвЂ™t start talking about the issue without explaining what Query Folding is, so letвЂ™s start with that. If you like to learn more about Power BI read the Power BI online book from Rookie to Rock Star. Note that Merge Columns is an example here ,this situation might happens with some other transformations as well. In this post IвЂ™ll show you how this cause the performance issue, and how it can be solved. This easiness does come with a price, a high price IвЂ™d say, price of reducing the performance of Power Query and as a result Power BI! Merge Columns doesnвЂ™t support query folding and it means it will affect performance badly. Now with the new Merge Column this are much easier, you select columns and apply Merge Columns. Previously you could do the concatenation with adding concatenation simply with & character, what youвЂ™ve done was adding a new custom column, and writing M expression to concatenate columns. Merge Columns concatenate columns to each other to either create a new column, or replace them with the new concatenated result. as an example recently (I believe from last few releases) Power BI Desktop added a feature called Merge Columns. Some transformations can be translated to query language of the data source, some not. Have you ever thought does your Power Query Transformations happens in the data source side (server side), or on the local memory (client side)? When you use a relational data source, query can be run on the data source, but it depends on transformations.

Adventureworks2012 diagram