Text format is not applying as expected within Power Query

[BITomS]

Hi, I have a custom column in Power Query that works in one pbix file, but when I copy the logic across to another pbix file, the formatting applies differently to expectation (the previous file is as expected). Source tables and data types are exactly the same between the 2 files, so it looks like it is only the way the formatting is being applied that's the issue.

For context, I am concatenating a date field ([ProfileStartDate]) with a text field ([Supplier]) and a number ([Rank]) to form a single text string that can be used as an identifier in the end report.

My expectation is that the "00" formatting in bold below, allows for the month and day to be (e.g.) 04 instead of just 4 to ensure consistent date length within the string. In the original file, I get a result like 2024/04/01 - Test - 1, whereas in the subsequent file, I am getting a result of 2024/4/1 - Test - 1. Has there been any update to PBI that may have caused this? Or are there any file settings I can check to ensure consistency between the 2 files? Any help most appreciated!

The Custom Column step for reference:

= Number.ToText ( Date.Year ( [ProfileStartDate] ) ) & "/" & Number.ToText (Date.Month ( [ProfileStartDate] ) , "00" ) & "/" & Number.ToText ( Date.Day ( [ProfileStartDate] ) , "00" ) & " - " & [Supplier] & " - " & Number.ToText ( [Rank] )

[Anonymous]

Hi @BITomS ,

Please double-check the Power Query settings in both files. Ensure that the data types for the columns involved in the custom column are consistent. Sometimes, even a slight difference in data type can cause formatting issues.

If the issue persists, you might want to try an alternative approach to ensure consistent formatting. For example, you can use the Text.PadStart function to pad the month and day values with leading zeros:

= Text.From(Date.Year([ProfileStartDate])) & "/" & Text.PadStart(Text.From(Date.Month([ProfileStartDate])), 2, "0") & "/" & Text.PadStart(Text.From(Date.Day([ProfileStartDate])), 2, "0") & " - " & [Supplier] & " - " & Text.From([Rank])

Best regards.
Community Support Team_Caitlyn

[BITomS]

@Anonymous , I've triple checked the data types and they're identical. Your alternative approach is exactly what I was looking for, so thank you very much for this, it's worked perfectly!

Please feel free to mark as closed.