Need to add a period in the codes for a column

[Holly_AZ_U2]

Hi...

I have values in the range of F10 - F1999 in a column that I need to add a period after the 3rd digit of the 4 or 5 digit codes ( i.e., F10.0 or F10.00). I do not need a period for the 3 digit codes. 

I know I need to have the code column duplicated and customized. Just not sure of the coding for it?

Thanks!

[danextian · Accepted solution]

Hi @Holly_AZ_U2 

Try these in the query editor

let 
length = Text.Length([Column]),
RemainingLength = length -3 
in
if length >3 then Text.Start([Column],3) & "." & Text.End([Column], RemainingLength) else [Column]

[v-sgandrathi]

Hi @Holly_AZ_U2,

We strive to make sure every issue gets addressed completely.

Still facing issues? Just reply here and we’ll be right with you.

Thanks for being a valued part of the community,

[v-sgandrathi]

Hi @Holly_AZ_U2,

Thank you @cengizhanarslan @Lodha_Jaydeep @danextian @Future_To_BI  for your responses to the query.

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help.

Thank you.

[cengizhanarslan]

Please try this custom column formula in Power Query:

= if Text.Length([Code]) > 3
then Text.Start([Code], 3) & "." & Text.Middle([Code], 3)
else [Code]

[danextian · Accepted solution]

Hi @Holly_AZ_U2 

Try these in the query editor

let 
length = Text.Length([Column]),
RemainingLength = length -3 
in
if length >3 then Text.Start([Column],3) & "." & Text.End([Column], RemainingLength) else [Column]

[Future_To_BI]

Hi @Holly_AZ_U2

You can do this in Power BI → Transform Data (Power Query) by adding a custom column.

Steps

1. Go to Transform Data
2. Select your column (e.g., Code)
3. Click Add Column → Custom Column
4. Use this formula:
   = if Text.Length([Code]) <= 4
   then [Code]
   else Text.Start([Code], 3) & "." & Text.Range([Code], 3)
    If this solved your problem, please mark it as the solution

[Lodha_Jaydeep]

Hi @Holly_AZ_U2,

Thanks for reaching fabric community, will happy to assist.

If you want to transform in the power query below are the steps to do the same.

Example step for Power query

if Text.Length([Code]) > 3
then Text.Start([Code], 3) & "." & Text.Range([Code], 3)
else [Code]

 If you want for greater than 3 above step will give the accurate result.

Below is the calculated column logic

Formatted Code =
VAR CodeText = 'Table'[Code]
RETURN
IF (
    LEN ( CodeText ) > 3,
    LEFT ( CodeText, 3 ) & "." & MID ( CodeText, 4, LEN ( CodeText ) - 3 ),
    CodeText
)

Please consider this as an accpeted soltuin if helps or give some kudos.
Thanks

[Lodha_Jaydeep · Accepted solution]

This will looks like below

let
  Source = Table.FromRows(
    Json.Document(
      //Binary.Decompress(Binary.FromText(YourSource, BinaryEncoding.Base64), Compression.Deflate)
      YourSource
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Data = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Data", type text}}),
  #"Added Formatted Code" = Table.AddColumn(
    #"Changed Type",
    "Formatted Code",
    each
      if Text.Length([Data]) > 3 then
        Text.Start([Data], 3) & "." & Text.Range([Data], 3)
      else
        [Data],
    type text
  )
in
  #"Added Formatted Code"