cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

Accepted Solutions
Highlighted
Super User II
Super User II

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

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
Highlighted
Super User IV
Super User IV

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

@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?






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.





Highlighted
Super User II
Super User II

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

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

Highlighted
Frequent Visitor

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

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

Is that really a space or something else?

Highlighted
Super User II
Super User II

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

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors