Create a YYYYMMDD Column without breaking Query Folding

Hi  Anonymous ,

Take below M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUTJUitUBcwxBHAMUnhEyz1IpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "FullDate", each [Year] * 10000 + [Month] * 100 + 01),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"FullDate", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.Length(Text.From([Month]))=1  then Text.PadStart(Text.From([Month]),2,"0") else Text.From([Month])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each Text.From([Year])&[Custom]&"01"&"T00:00:00"),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.2", each DateTime.FromText([Custom.1]))
in
    #"Added Custom3"

Finally you will see:

It can be used in incremental refresh.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!