INT CONCATENATE not working on a column with blanks

[Peacee]

Hello,

I have a created column with which I re-format dates into YYYY-WW. It is working just fine as it is referring to a column without any blanks. Here is the created column's formula:

I created a different column using the same formula on a different date column, although this column has some blank rows. As I cannot remove those blank rows, i'm having a hard time finding a solution. Here is the formula along with it's error:

Can anyone provide some help on the matter? I'm fairly new to PBI and I've been running in circles trying to find a solution.
Thanks!

[Ahmedx · Accepted solution]

try writing like this

Open YearWeek =
IF([u_register_date],
YEAR ( 'Files BI’[u_register_date] )*100 + WEEKNUM ('Files BI’[u_register_date],1 ))

-------or ------
Open YearWeek =
IFERROR(
YEAR ( 'Files BI’[u_register_date] )*100 + WEEKNUM ('Files BI’[u_register_date],1 ),,BLANK())
-------or------

Open YearWeek =
IFERROR(
YEAR (DATEVALUE( 'Files BI’[u_register_date] ))*100 + WEEKNUM (DATEVALUE('Files BI’[u_register_date]),1 ),,BLANK())

[Ahmedx]

you checked my solution?

[Peacee]

Hey Admedx,

I just checked it out, apologies for the delay I was out of town. It works perfectly! Thanks so much! 🙂

[parry2k]

@Peacee maybe try this, check blank first and then do the calculation

New Column = IF ( NOT ISBLANK ( Table[Column] ), <<your concatenate formula here>> )

[Pady]

Hello Peacee,

Hope this helps:

[Peacee]

Thanks for your response Pady,

Unfortunately this is just the same formula that I posted. It would not work if one of those date rows were blank. As stated at the start of my post, the formula works, it just doesn't work when there's a blank row. Since I can't remove the rows with a blank date, I need to find a way around it.

[Pady]

In case you are fine with the 'Text' data type for the new column, you can remove INT()