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 column with a 0, and then add in a default 01 for the day?

 

So take February 1st, 2021 for example. My year column would be the number 2021 and my month column would be the number 2. 

 

How can I create a column that would return 20210201 as a number without breaking query folding? Is it possible?

 

I tried Text.PadStart(Text.From) and then making that custom column a number, but query editor still thinks it's a text column even though it says number.  

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!

     

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello Anonymous ,
    You can use the below DAX formula for a calculated column in the calendar table.
    YearMonthDay =
    VALUE (
    CONCATENATE (
    FORMAT ( 'Date'[Date], "YYYY" ),
    CONCATENATE (
    ( RIGHT ( "0" & MONTH ( 'Date'[Date] ), 2 ) ),
    ( RIGHT ( "0" & DAY ( 'Date'[Date] ), 2 ) )
    )
    )
    )

  • PhilipTreacy's avatar
    PhilipTreacy
    Super 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
        Super 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

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    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!