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!

     

     

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

Hi Anonymous 

Not clear if you need this in PQ or DAX.  Download a sample PBIX with PQ code

This code will create a column in PQ that is type Whole Number, this will get added into the Data Model too

 

= Table.AddColumn(#"Changed Type", "FullDate", each [Year] * 10000 + [Month] * 100 + 01)

 

This is the full example query

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}})
in
    #"Changed Type1"

Regards

Phil

  • 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