cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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
1 ACCEPTED SOLUTION
Helper I
Helper I

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.

View solution in original post

6 REPLIES 6
Helper V
Helper V

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

Helper I
Helper I

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.

View solution in original post

Super User II
Super User II

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

Super User III
Super User III

Hi @Jase71ds ,

 

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

Pragati11_0-1606147475960.png

 

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

Pragati11_1-1606147554132.png

 

This will chnage the data-type of your column.

 

Thanks,

Pragati

 





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




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

 

nel mio pc il risultato è questo.

Non è quello che cercavi?

 

image.png

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors