Query Folding disables with Split Column Power Query Function

Power Query
1

Hi ALL,

I want to split a column holding values YYYYMMDD into YYYYMM without breaking the Native Query Folding(Datasource - SQL Server) , I tried using the Power Query Split Option and also by creating custom column and using Power Query Text.Start function. Is it possible to use Power query function without breaking Query Folding.

@Melissa - It would be of great help if you can provide your valuable inputs.

2

Hi @Vishy,

I think you’ll find this article helpful.
https://docs.microsoft.com/en-us/power-query/power-query-folding

.

Transformations that can be query folded include adding custom columns with simple logic (SELECT column expressions). Simple logic implies uncomplicated operations, possibly including the use of M functions that have equivalent functions in the SQL data source, like mathematic or text manipulation functions.

So you’ll have to explore possible SQL expressions that can be translated.

3

@Melissa ,

I did test this but it is break query folding

4

SQL Server SUBSTRING() Function

Can be translated to:
Text.Middle(“Hello World”, 6, 5)

I apologize I’m just guessing here… don’t have any experience with SQL

5

I think the only option you have is of pushing that step towards the end as much as possible.

6

Hi @Vishy

Try and add another column to your query and try and use
Coalesce year(CAST(date column AS DATE)) , ( month(CAST(date column AS DATE)) )
to get yyyymm.

If it doesn’t work as suggested by @Melissa you can use substring( date column ) in Sql as well.

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

7

Thanks @kkrj.ankit & @AntrikshSharma - for the Valuable inputs i believe moving the split step to the last is the only way as I need to do the transformation in Power query and not SQL.

8

Hi @Vishy, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!