Forum Discussion

Jase71ds's avatar
Jase71ds
Icon for Advocate I rankAdvocate I
5 years ago
Solved

Need to Force a Data Column to Make Numbers into Text in the Form of XX.XX

Hoping for some Power Query assistance.

I have a column of reference data that I want to treat as Text, and have it in the form of xx.xx

In Excel - I would simply highlight the column, Format/Custom, and type in "00.00" to get my desired outcome.

I've spent an hour in PQ and can't make this work. Can anyone offer some guidance?

Much appreciated!

Jase.

Column as shown in Power QueryDesired (text) outcome
1.2101.21
1.2201.22
1.301.30
10.110.10
12.212.20
Need Help
  • Jase71ds's avatar
    Jase71ds
    5 years ago

    Thanks for this. I struggled with it (I'm not the brightest) but to no avail. However, your answer prompted me to success. It's certainly not elegant, but here's what I did...

    Split the column at the "." delimeter.

    Renamed the two new columns, "Half_1" and "Half_2"

    Created 2 custom columns:

    • = Text.PadStart( [Half_1], 2 )
    • = Text.PadEnd( [Half_2], 2 )

    This put a space any time there was not 2 digits

    Then I went and did a Find/Replace " " with "0"

    Then concatenated the two columns: = [Half_1] & "." & [Half_2]

    Long way to do it, but it worked!

    Thanks to all, your post prompted me in a successful direction!

    Jase.

6 Replies

  • Jase71ds's avatar
    Jase71ds
    Icon for Advocate I rankAdvocate I
    5 years ago

    Thanks for this. I struggled with it (I'm not the brightest) but to no avail. However, your answer prompted me to success. It's certainly not elegant, but here's what I did...

    Split the column at the "." delimeter.

    Renamed the two new columns, "Half_1" and "Half_2"

    Created 2 custom columns:

    • = Text.PadStart( [Half_1], 2 )
    • = Text.PadEnd( [Half_2], 2 )

    This put a space any time there was not 2 digits

    Then I went and did a Find/Replace " " with "0"

    Then concatenated the two columns: = [Half_1] & "." & [Half_2]

    Long way to do it, but it worked!

    Thanks to all, your post prompted me in a successful direction!

    Jase.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Here you go, this works.

     

    let
    Source = NameOfYourTable,
    #"Added Prefix" = Table.TransformColumns(Source, {{"Column as shown in Power Query", each if Text.StartsWith(_, "1.") then ("0" & _) else _, type text}}),
    #"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"Column as shown in Power Query", each if Text.Length(_) <> 5 then _ & "0" else _, type text}})
    in
    #"Added Suffix"

     

    Column as shown in Power QueryDesired (text) outcome

    01.2101.21
    01.2201.22
    01.3001.30
    10.1010.10
    12.2012.20

    ---Nate

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    5 years ago

    Hi Jase71ds ,

     

    What you can do is go to Query Editor in Power BI. Select the column in your table:

     

    In the top ribbon select TRANSFORM --> Data Type --> TEXT:

     

    This will chnage the data-type of your column.

     

    Thanks,

    Pragati

     

    • Jase71ds's avatar
      Jase71ds
      Icon for Advocate I rankAdvocate I
      5 years ago

      Thanks but I've already tried that. Doesn't produce desired results 😞

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

         

        nel mio pc il risultato è questo.

        Non è quello che cercavi?

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    this is a workaround

    = Table.AddColumn(Origine, "format", each let part=Text.Split([Column as shown in Power Query],".") in Text.PadStart(part{0},2,"0")&"."&Text.PadEnd(part{1},2,"0"))

     

    perphs the Text.Format function could accomplish this task better, but I dont have the documentation of that function