The (Almost) Definitive Guide to Query Folding

There’s only one rule. Don’t break the fold! Before we start enforcing all these rules, we should start with something like a solid foundation of what does and doesn’t fold in Power Query and their SQL equivalents. Now, if you found this page and we’re like “what in the world is query folding?” read this incredible article, from this amazing author.

The disclaimer thing: This is a personal list compiled through testing during the 30-day query folding challenge either done by myself or members of the community (❤️). Every scenario is different, and every situation is unique. The functionality described in this article is subject to change and may evolve over time. Please feel free to leave comments at the end of the article or find me via the social channels to let me know of revisions.

About the data source: The source utilized for these scenarios was an Azure SQL Database or SQL Server. There may not be equal folding functionality within other systems, and you should always test your own solutions to determine full functionality before production use.

Name	Supports Folding	SQL Equivalent
Binary.Buffer	FALSE
Binary.Compress	FALSE
Binary.ApproximateLength	FALSE
Binary.Decompress	FALSE
Binary.ViewError	FALSE
Binary.ToText	FALSE
Binary.Type	FALSE
Binary.View	FALSE
Binary.ViewFunction	FALSE
Binary.Combine	FALSE
Binary.ToList	FALSE
Binary.Range	FALSE
Binary.FromList	FALSE
Binary.Length	FALSE
Binary.InferContentType	FALSE
Binary.From	FALSE
Binary.FromText	FALSE
Byte.Type	FALSE
Byte.From	FALSE
Character.FromNumber	FALSE	CHAR
Character.ToNumber	FALSE
Currency.Type	FALSE
Currency.From	FALSE
Date.IsLeapYear	FALSE
Date.From	FALSE
Date.StartOfDay	FALSE
Date.MonthName	FALSE
Date.EndOfYear	FALSE
Date.EndOfDay	FALSE
Date.DaysInMonth	FALSE
Date.EndOfWeek	FALSE
Date.EndOfQuarter	FALSE
Date.WeekOfMonth	FALSE
Date.ToText	FALSE
Date.Type	FALSE
Date.FromText	FALSE
Date.ToRecord	FALSE
Date.StartOfQuarter	FALSE
Date.StartOfMonth	FALSE
Date.StartOfYear	FALSE
Date.StartOfWeek	FALSE
Date.DayOfWeekName	FALSE	DATENAME
Date.IsInCurrentDay	TRUE
Date.IsInCurrentMonth	TRUE
Date.EndOfMonth	TRUE	EOMONTH
Date.WeekOfYear	TRUE	DATEPART
Date.Year	TRUE	DATEPART
Date.IsInNextDay	TRUE
Date.IsInNextMonth	TRUE
Date.IsInCurrentYear	TRUE
Date.IsInCurrentQuarter	TRUE
Date.IsInCurrentWeek	TRUE
Date.AddWeeks	TRUE	DATEADD
Date.AddYears	TRUE	DATEADD
Date.AddQuarters	TRUE	DATEADD
Date.AddDays	TRUE	DATEADD
Date.AddMonths	TRUE	DATEADD
Date.Month	TRUE	DATEPART
Date.QuarterOfYear	TRUE	DATEPART
Date.DayOfYear	TRUE	DATEPART
Date.Day	TRUE	DATEPART
Date.DayOfWeek	TRUE	DATEPART
Date.IsInPreviousNQuarters	TRUE
Date.IsInPreviousNWeeks	TRUE
Date.IsInPreviousNMonths	TRUE
Date.IsInPreviousMonth	TRUE
Date.IsInPreviousNDays	TRUE
Date.IsInPreviousYear	TRUE
Date.IsInYearToDate	TRUE
Date.IsInPreviousWeek	TRUE
Date.IsInPreviousNYears	TRUE
Date.IsInPreviousQuarter	TRUE
Date.IsInNextNQuarters	TRUE
Date.IsInNextNWeeks	TRUE
Date.IsInNextNDays	TRUE
Date.IsInNextNMonths	TRUE
Date.IsInNextNYears	TRUE
Date.IsInPreviousDay	TRUE
Date.IsInNextYear	TRUE
Date.IsInNextQuarter	TRUE
Date.IsInNextWeek	TRUE
DateTime.FromText	FALSE
DateTime.Type	FALSE
DateTime.IsInPreviousNSeconds	TRUE
DateTime.IsInPreviousSecond	TRUE
DateTime.IsInPreviousNMinutes	TRUE
DateTime.IsInPreviousMinute	TRUE
DateTime.IsInPreviousNHours	TRUE
DateTime.LocalNow	TRUE
DateTime.Time	FALSE
DateTime.ToRecord	FALSE
DateTime.FixedLocalNow	FALSE
DateTime.AddZone	FALSE
DateTime.Date	FALSE
DateTime.IsInPreviousHour	TRUE
DateTime.IsInCurrentMinute	TRUE
DateTime.IsInCurrentSecond	TRUE
DateTime.IsInCurrentHour	TRUE
DateTime.From	TRUE	CONVERT
DateTime.FromFileTime	TRUE
DateTime.IsInNextHour	TRUE
DateTime.IsInNextNSeconds	TRUE
DateTime.IsInNextSecond	TRUE
DateTime.IsInNextNMinutes	TRUE
DateTime.IsInNextMinute	TRUE
DateTime.IsInNextNHours	TRUE
DateTime.ToText	FALSE
DateTimeZone.ToUtc	FALSE
DateTimeZone.ToText	FALSE
DateTimeZone.ZoneHours	FALSE
DateTimeZone.ToRecord	FALSE
DateTimeZone.ZoneMinutes	FALSE
DateTimeZone.RemoveZone	FALSE
DateTimeZone.FromText	FALSE
DateTimeZone.SwitchZone	FALSE
DateTimeZone.From	FALSE
DateTimeZone.ToLocal	FALSE
DateTimeZone.FixedUtcNow	TRUE
DateTimeZone.FromFileTime	TRUE
DateTimeZone.Type	FALSE
DateTimeZone.FixedLocalNow	TRUE
DateTimeZone.UtcNow	TRUE
DateTimeZone.LocalNow	TRUE
Decimal.Type	FALSE
Decimal.From	TRUE
Double.Type	FALSE
Double.From	TRUE	CONVERT
Duration.Minutes	FALSE
Duration.Type	FALSE
Duration.TotalMinutes	FALSE
Duration.TotalSeconds	FALSE
Duration.TotalHours	FALSE
Duration.Seconds	FALSE
Duration.ToRecord	FALSE
Duration.ToText	FALSE
Duration.Days	FALSE
Duration.TotalDays	TRUE	DATEDIFF
Duration.From	FALSE
Duration.Hours	FALSE
Duration.FromText	FALSE
Guid.From	FALSE
Guid.Type	FALSE
Int16.From	FALSE
Int16.Type	FALSE
Int32.Type	FALSE
Int32.From	FALSE
Int64.Type	FALSE
Int64.From	FALSE
Int8.From	FALSE
Int8.Type	FALSE
Json.FromValue	FALSE
Json.Document	FALSE
List.SingleOrDefault	FALSE
List.Skip	FALSE
List.Split	FALSE
List.Single	FALSE
List.ReplaceRange	FALSE
List.ReplaceValue	FALSE
List.Reverse	FALSE
List.StandardDeviation	FALSE
List.Type	FALSE
List.Union	FALSE
List.Zip	FALSE
List.TransformMany	FALSE
List.Sum	FALSE
List.Times	FALSE
List.Transform	FALSE
List.ReplaceMatchingItems	FALSE
List.Distinct	FALSE
List.Durations	FALSE
List.FindText	FALSE
List.Difference	FALSE
List.Dates	FALSE
List.DateTimes	FALSE
List.DateTimeZones	FALSE
List.Intersect	FALSE
List.IsDistinct	FALSE
List.IsEmpty	FALSE
List.InsertRange	FALSE
List.First	FALSE
List.FirstN	FALSE
List.Generate	FALSE
List.Covariance	FALSE
List.Sort	TRUE
List.Accumulate	FALSE
List.AllTrue	FALSE
List.Select	TRUE
List.Contains	TRUE	IN
List.Combine	TRUE
List.RemoveItems	TRUE
List.ContainsAll	FALSE
List.ContainsAny	FALSE
List.Count	FALSE
List.Buffer	FALSE
List.Alternate	FALSE
List.AnyTrue	FALSE
List.Average	FALSE
List.Last	FALSE
List.Positions	FALSE
List.Product	FALSE
List.Random	FALSE
List.PositionOfAny	FALSE
List.NonNullCount	FALSE
List.Numbers	FALSE
List.PositionOf	FALSE
List.RemoveNulls	FALSE
List.RemoveRange	FALSE
List.Repeat	FALSE
List.RemoveMatchingItems	FALSE
List.Range	FALSE
List.RemoveFirstN	FALSE
List.RemoveLastN	FALSE
List.Modes	FALSE
List.MinN	FALSE
List.Mode	FALSE
List.MaxN	FALSE
List.Min	FALSE
List.Max	FALSE
List.LastN	FALSE
List.Median	FALSE
List.MatchesAny	FALSE
List.MatchesAll	FALSE
Logical.ToText	FALSE
Logical.FromText	FALSE
Logical.Type	FALSE
Logical.From	FALSE
Null.Type	FALSE
Number.Sinh	FALSE
Number.Sin	FALSE
Number.Type	FALSE
Number.Tanh	FALSE
Number.Exp	TRUE	EXP
Number.RoundDown	TRUE	FLOOR
Number.Cos	TRUE	COS
Number.From	TRUE	CONVERT
Number.ToText	TRUE	CONVERT
Number.Power	TRUE	POWER
Number.Round	TRUE	ROUND
Number.Mod	TRUE	MOD
Number.Log	TRUE	LOG
Number.Log10	TRUE	LOG10
Number.Epsilon	TRUE	4.940656458412465E-324
Number.Abs	TRUE	ABS
Number.Pi	TRUE
Number.PositiveInfinity	FALSE
Number.E	TRUE
Number.Atan2	TRUE	ATN2
Number.RoundUp	TRUE	CEILING
Number.Atan	TRUE	ATAN
Number.Acos	TRUE	ACOS
Number.Asin	TRUE	ASIN
Number.Sqrt	TRUE	SQRT
Number.IsEven	FALSE
Number.IsNaN	FALSE
Number.IntegerDivide	FALSE
Number.Factorial	FALSE
Number.FromText	FALSE
Number.NegativeInfinity	FALSE
Number.Permutations	FALSE
Number.NaN	FALSE
Number.IsOdd	FALSE
Number.Ln	FALSE
Number.BitwiseNot	FALSE
Number.BitwiseOr	FALSE
Number.BitwiseAnd	FALSE
Number.Tan	TRUE	TAN
Number.Random	TRUE
Number.Combinations	FALSE
Number.Cosh	FALSE
Number.BitwiseXor	FALSE
Number.BitwiseShiftLeft	FALSE
Number.BitwiseShiftRight	FALSE
Number.RoundTowardZero	FALSE
Number.RandomBetween	FALSE
Number.RoundAwayFromZero	FALSE
Number.Sign	FALSE
Percentage.Type	FALSE
Percentage.From	FALSE
Single.Type	FALSE
Single.From	FALSE
Table.Column	FALSE
Table.Buffer	FALSE
Table.Transpose	TRUE
Table.UnpivotOtherColumns	TRUE
Table.TransformColumnNames	TRUE
Table.AddIndexColumn	FALSE
Table.Unpivot	TRUE
Table.AddKey	FALSE
Table.AlternateRows	FALSE
Table.AggregateTableColumn	FALSE
Table.ColumnCount	FALSE
Table.ReplaceMatchingRows	FALSE
Table.ReplaceRelationshipIdentity	FALSE
Table.ReplaceKeys	FALSE
Table.AddJoinColumn	FALSE
Table.ReplaceErrorValues	FALSE
Table.TransformRows	FALSE
Table.ViewFunction	FALSE
Table.TransformColumnTypes	FALSE
Table.ReplaceRows	FALSE
Table.ToColumns	FALSE
Table.ReorderColumns	TRUE
Table.Range	TRUE	TOP
Table.FirstN	TRUE	TOP
Table.SelectColumns	TRUE	SELECT
Table.SelectRows	TRUE	WHERE
Table.Repeat	TRUE	UNION ALL
Table.Combine	TRUE	UNION ALL
Table.Distinct	TRUE	DISTINCT
Table.ReplaceValue	TRUE	CASE
Table.PrefixColumns	FALSE
Table.Pivot	TRUE	PIVOT
Table.Sort	TRUE	ORDER BY
Table.Group	TRUE	GROUP BY
Table.AddColumn	TRUE
Table.NestedJoin	TRUE
Table.Join	TRUE
Table.FromPartitions	TRUE
Table.RenameColumns	TRUE
Table.RemoveColumns	TRUE
Table.Partition	TRUE
Table.DuplicateColumn	TRUE
Table.ColumnsOfType	TRUE
Table.ColumnNames	TRUE
Table.ExpandTableColumn	TRUE
Table.ExpandRecordColumn	TRUE
Table.ExpandListColumn	TRUE
Table.FuzzyNestedJoin	FALSE
Table.HasColumns	FALSE
Table.InsertRows	FALSE
Table.FromValue	FALSE
Table.FuzzyGroup	FALSE
Table.FuzzyJoin	FALSE
Table.IsDistinct	FALSE
Table.LastN	FALSE
Table.MatchesAllRows	FALSE
Table.MatchesAnyRows	FALSE
Table.IsEmpty	FALSE
Table.Keys	FALSE
Table.Last	FALSE
Table.FromRows	FALSE
Table.DemoteHeaders	FALSE
Table.FillDown	FALSE
Table.FillUp	FALSE
Table.Contains	FALSE
Table.ContainsAll	FALSE
Table.ContainsAny	FALSE
Table.FilterWithDataTable	FALSE
Table.FromColumns	FALSE
Table.FromList	FALSE
Table.FromRecords	FALSE
Table.FindText	FALSE
Table.First	FALSE
Table.FirstValue	FALSE
Table.Max	TRUE	TOP
Table.SelectRowsWithErrors	FALSE
Table.SingleRow	FALSE
Table.Skip	FALSE
Table.ReverseRows	FALSE
Table.RowCount	FALSE
Table.Schema	FALSE
Table.Split	FALSE
Table.ToRows	FALSE
Table.TransformColumns	FALSE
Table.View	FALSE
Table.SplitColumn	FALSE
Table.ToList	FALSE
Table.ToRecords	FALSE
Table.RemoveRowsWithErrors	FALSE
Table.PartitionValues	FALSE
Table.PositionOf	FALSE
Table.PositionOfAny	FALSE
Table.MaxN	TRUE	TOP
Table.Min	TRUE	TOP
Table.MinN	TRUE	TOP
Table.CombineColumns	FALSE
Table.RemoveLastN	FALSE
Table.RemoveMatchingRows	FALSE
Table.RemoveRows	FALSE
Table.Profile	FALSE
Table.PromoteHeaders	FALSE
Table.RemoveFirstN	FALSE
Text.ToBinary	FALSE
Text.ToList	FALSE
Text.SplitAny	FALSE
Text.Select	FALSE
Text.Split	FALSE
Text.AfterDelimiter	FALSE
Text.Range	FALSE	SUBSTRING
Text.BeforeDelimiter	FALSE
Text.Type	FALSE
Text.BetweenDelimiters	FALSE
Text.RemoveRange	FALSE
Text.Insert	FALSE
Text.Middle	FALSE
Text.InferNumberType	FALSE
Text.Format	FALSE
Text.FromBinary	FALSE
Text.Clean	FALSE
Text.Remove	FALSE
Text.PositionOfAny	FALSE
Text.PadEnd	FALSE
Text.PadStart	FALSE
Text.At	FALSE	SUBSTRING
Text.Start	TRUE	LEFT
Text.From	TRUE	CONVERT
Text.Length	TRUE	LEN
Text.TrimStart	TRUE	LTRIM
Text.Lower	TRUE	LOWER
Text.Contains	TRUE	CASE
Text.Proper	FALSE
Text.EndsWith	TRUE	CASE
Text.Combine	TRUE	CONCAT
Text.StartsWith	TRUE	CASE
Text.PositionOf	FALSE	CHARINDEX
Text.NewGuid	TRUE
Text.Repeat	FALSE	REPLICATE
Text.ReplaceRange	FALSE	STUFF
Text.Reverse	FALSE	REVERSE
Text.End	TRUE	RIGHT
Text.Replace	TRUE	REPLACE
Text.TrimEnd	TRUE	RTRIM
Text.Upper	TRUE	UPPER
Text.Trim	TRUE	TRIM
Time.Type	FALSE
Time.ToText	FALSE
Time.Second	FALSE
Time.ToRecord	FALSE
Time.StartOfHour	FALSE
Time.FromText	FALSE
Time.EndOfHour	FALSE
Time.From	FALSE
Time.Hour	FALSE
Time.Minute	FALSE
Value.Add	FALSE
Value.Compare	FALSE
Value.Divide	FALSE
Value.Multiply	FALSE
Value.Equals	TRUE	CASE
Value.As	TRUE
Value.FromText	FALSE
Value.NullableEquals	FALSE
Value.Firewall	FALSE
Value.RemoveMetadata	FALSE
Value.ReplaceType	FALSE
Value.ReplaceMetadata	FALSE
Value.Metadata	FALSE
Value.Is	FALSE
Value.ResourceExpression	FALSE
Value.Subtract	FALSE
Value.NativeQuery	FALSE

4 thoughts on “The (Almost) Definitive Guide to Query Folding”

Michael Carrabine says:

March 25, 2024 at 2:16 pm

This is a great resource.

I am trying to get the domain from an email address. However, unfortunately, folding is not working with Text.PositionOf against a SQL Server on prem data source in a Dataflow Gen2. The folding indicator is displaying “Analysis on this step was inconclusive.”

1. Alex Powers says:
  
  April 7, 2024 at 9:26 pm
  
  The folding indicators are a “guide” but not always the truth is what I have found at times, if you check the logs against the SQL Server does it say if the statement is being properly generated?
  
Tri says:

April 3, 2024 at 6:07 pm

This is pretty cool, are there updates to this list?

1. Alex Powers says:
  
  April 7, 2024 at 9:25 pm
  
  As the engineering team adds support for more functions to fold, I absolutely try to keep this updated. Or if you find something that works that I did not have documented, please let me know.

It's Not About The Cell

Hiding The Gridlines That Hold Us Back

The (Almost) Definitive Guide to Query Folding

Related

4 thoughts on “The (Almost) Definitive Guide to Query Folding”

Questioning My Mental Health?Cancel reply

Share this:

Related

4 thoughts on “The (Almost) Definitive Guide to Query Folding”

Questioning My Mental Health?Cancel reply