Forum Discussion

bprakash's avatar
bprakash
Regular Visitor
8 years ago

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...
RahulYadav's avatar
RahulYadav
Resolver II
8 years ago

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's avatar
bprakash
Regular Visitor
8 years ago

Thanks everyone

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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"})

    Substring_in_Power_BI

     

     

    Best Regards,

    Dale