Concatenate month & year columns into an int type column and add a leading zero in front of month

[Quiny_Harl]

Hi,

I have columns year and month of type int

YEAR	MONTH
2021	9
2021	10

I would like to combine them into a new column which need to be of type int as well and should have a leading zero in front of the month. Example: 202109

Note that I cannot create a text column and then change the type to int because that would screw up the query folding. 

Thanks!

[smpa01 · Accepted solution]

@Quiny_Harl  do you like to do this in server side or after you bring it over to PQ? 

in PQ this is how you can do it

Number.From(Text.From([Year])&(if(Text.Length(Text.From([Month]))=1) then Text.PadStart(Text.From([Month]),2,"0") else Text.From([Month])))

[Quiny_Harl]

@smpa01 

Thank you for your help. Actually, I accepted your answer as solution but then I saw that this solution prevents the query folding. Also, the resulting column is of type "any" and not not of type int. So unfortunatelly I cannot use it. 

[smpa01]

@Quiny_Harl  which RDBMS are you using? You can do this transformation on the server side and not to be worried about query folding at all. ARe you using a TSQL?

[Quiny_Harl]

It's not possible to do it on the server side. Otherwise, life would have been much easier 🙂

[smpa01]

@Quiny_Harl  like I asked what is the RDBMS?

and if it is TSQL, it is absolutely possible

declare @t1 as table (year int, month int)
insert into @t1
select * from
(values(2021,9),(2021,10)) t(a,b)

select *
, CASE WHEN LEN(month)=1 then convert(bigint,convert(nvarchar,year)+'0'+ convert(nvarchar,month))
       WHEN LEN(month)>1 then convert(bigint,convert(nvarchar,year)+ convert(nvarchar,month))
end as test
from @t1

[Quiny_Harl]

Thank you for your suggestion but I don't have permission to edit the database. 

[smpa01]

@Quiny_Harl  if it is TSQL, you don't need to have the database edit rights. This code will work on any table, any views. I created the table to demonstarte my point. Simply run the following portion

select *
, CASE WHEN LEN(month)=1 then convert(bigint,convert(nvarchar,year)+'0'+ convert(nvarchar,month))
       WHEN LEN(month)>1 then convert(bigint,convert(nvarchar,year)+ convert(nvarchar,month))
end as test
from transactionTBL

Writing a query on the database does not need edit right. Please check with youur DBA. As long as you can query the table from databse (as you are doing it now), you will not have issues running this native query.