Forum Discussion
Substring in Power BI ?!
I have a text date field in format FY18-Q2-5, last digit being the week number (here in this example 5 is the week number). I want to substring 5 from above date field & put '0' infront of week n...
You can try using this function, you may have to check the LEN of the week as you will be doing this only until 9.
MID(Txt, StartPosition, NumberOfCharacters)
I would offer a different solution, i would try this code (a similar approach could also be done in Power Query, which is where it would be better placed).
Create calculated column with:
Fixed Date = IF(
value(RIGHT([DateField], 2)) >= 0,
[DateField],
LEFT([DateField], LEN([DateField]) - 1) & "0" & RIGHT([DateField], 1)
)
In short, this will check the last 2 digits. If the number is between 0 and 9, you would have something like "-5" which it will see as negative 5. Thus if it is a positive number, you must have a 2 digit number.