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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Add mutiple space at the starting of column which has text values

I need to add some   spaces at the beginning of the column and that column has text values.

I believe this is technically and logically possible but why it is not working in power bi.

option1:in power query editor , created a custom column ;

if Text.Length([vdo_nm])<30 then (Text.PadStart([vdo_nm],30-Text.Length([vdo_nm])," "))
else
[vdo_nm]

which des not work in final report.

 

Option2: Created a new column using dax

(" " & app_strm_cntnt_chnl_pgm_mnth_agg[vdo_nm])

It is not working also.

 

Any idea how to solve?

1 ACCEPTED SOLUTION
edhans
Super User
Super User

It works - you can see it in the table view. But the visuals effectivly trim the beginning. Use the non-breaking space160 char instead.

 

 

Text.Length([vdo_nm])<30 then (Text.PadStart([vdo_nm],30-Text.Length([vdo_nm]),Character.FromNumber(160))

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
edhans
Super User
Super User

It works - you can see it in the table view. But the visuals effectivly trim the beginning. Use the non-breaking space160 char instead.

 

 

Text.Length([vdo_nm])<30 then (Text.PadStart([vdo_nm],30-Text.Length([vdo_nm]),Character.FromNumber(160))

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

if the length of a text is 12, then will it add 30-12=18 spaces .

Is that really a space or something else?

It is not a space, which is ASCII 32. It is a non-breaking space, but it takes up the same width. In the image below, this is 30 of the code 160 spaces before the "FR" character. The "Custom" column is nearly the width of this image, so you can see it is allowing the "space" to remain.

2020-06-04 16_38_30-Untitled - Power BI Desktop.png

Try it. Just replace " " in your code with Character.FromNumber(60). 

Note that due to kerning spaces are not the same width unless you are changing your font to fixed width, like Courier New.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
parry2k
Super User
Super User

@saktisahoo2006 it should work, in the first method, try to add other characters and see if it pad with the character. Do you have step after that is cleaning or trimming the column?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.