Substring in Power BI ?!

[bprakash]

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 numbers till 9?

Please help with the above query.

Thanks

[RahulYadav]

Hi @bprakash,

You can also try following approach for getting the week number from the date field.

1. Go to Edit Queries .

2. Add a new custom column in Power Query Editor.

3. Add Below formula to the Custom Column. This will show the text in reverse order.

Text.Combine(List.Reverse(Text.ToList([Quarter])))

4. Click Close & Apply.

5. Now add new column using DAX formula.

Week Num = value(mid(Quarter[RevQuarter],1,find("-",Quarter[RevQuarter],1) -1))

Screenshot:

Thanks,

Rahul

[bprakash]

Thanks everyone

[v-jiascu-msft]

Hi @bprakash,

Solution can change the data directly by modifying the code in the Advanced Editor. Please the demo here.

 #"Replaced Value" = Table.ReplaceValue(#"Changed Type", each [Column1],each if Number.FromText(Text.AfterDelimiter([Column1], "-", 1)) < 10 then Text.Replace([Column1],"-" & Text.AfterDelimiter([Column1], "-", 1), "-0" & Text.AfterDelimiter([Column1], "-", 1)) else [Column1],Replacer.ReplaceText,{"Column1"})

Best Regards,

Dale

[deepu299]

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) 

[Anonymous]

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.