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

 

Please help with the above query.

 

Thanks

5 Replies

  • deepu299's avatar
    deepu299
    Advocate V
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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.

       

  • 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

      • 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