Create a YYYYMMDD Column without breaking Query Folding

[Anonymous]

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.  

[v-kelly-msft · Accepted solution]

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]

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]

@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]

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

[Anonymous]

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 ) )
)
)
)