Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Jase71ds
Advocate I
Advocate 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
Jase71ds
Advocate I
Advocate 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
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Jase71ds
Advocate I
Advocate 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.

Anonymous
Not applicable

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

Pragati11
Super User
Super User

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

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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

Anonymous
Not applicable

 

nel mio pc il risultato è questo.

Non è quello che cercavi?

 

image.png

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors