Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create a YYYYMMDD Column without breaking Query Folding

So I have a year column that comes in as a number (2021). I also have a month column that comes in as a number (2).   How can I create a column that will use the year column, but pad the month colu...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

PhilipTreacy Thanks this is definitely what I'm looking for. I'm not able to test it at the moment. That column will definitely work. My only hesitation is if the last step in your proposed code would break query folding? This would be used to implement incremental refresh so I can't have it break query fold. 

PhilipTreacy's avatar
PhilipTreacy
Icon for Super User rankSuper User
5 years ago

Hi Anonymous 

You can remove the Change Type step.  If you need to you can set the Data type inside the Data Model after data is loaded into it

 

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)
in
    #"Added Custom"

 

Not sure where you are loading data from so not sure if a Change Type would break query folding.  You can test and see?  Right click the step and see if View Native Query is available.  If it is then QF is ok.

Regards

Phil