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

Adding tabs or spaces in a column based on the length of another column using M

Hello, i am trying to add spaces in a column based on the length of another column. For example:

Column       Task
1                Task 1
1.1                   Task 1.1
1.1.1                     Task 1.1.1

So if the length of the Column is 1 the task will have no space, if the length is 3 the task will have 3 spaces, if the length is 4 the task will have 4 spaces added in the beginning of the string. Does it make sense? 

2 ACCEPTED SOLUTIONS
Jakinta
Solution Supplier
Solution Supplier

Add this as your next step. Source should be replaced with your previous step/table...

 

 

 

#"Added Custom" = Table.AddColumn(Source, "Task", each if Text.Length([Column])=1
then 
Text.Combine({"Task ",[Column]})
else
Text.Combine({Text.Combine(List.Repeat({" "},Text.Length([Column]))),"Task ",[Column]}))

 

 

View solution in original post

Fowmy
Super User IV
Super User IV

@renmello 

You can paste the following code on a blank query and check the Add Column step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1QGSenAaxIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn( Source, "Custom", 
    each 
        let len = Text.Length([Column1]), 
            txt = "Task " & Text.From([Column1])  
        in
        if len = 1 then txt else 
            Text.PadStart(" ", len) & txt, type text)
in
    #"Added Custom"


The result in Power Query won't show the leading spaces but you can view them in the Power BI data tab.

Fowmy_0-1620868617593.png

 

 



 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

3 REPLIES 3
renmello
Helper I
Helper I

Both solutions worked, thank you so much 🙂

Fowmy
Super User IV
Super User IV

@renmello 

You can paste the following code on a blank query and check the Add Column step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1QGSenAaxIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn( Source, "Custom", 
    each 
        let len = Text.Length([Column1]), 
            txt = "Task " & Text.From([Column1])  
        in
        if len = 1 then txt else 
            Text.PadStart(" ", len) & txt, type text)
in
    #"Added Custom"


The result in Power Query won't show the leading spaces but you can view them in the Power BI data tab.

Fowmy_0-1620868617593.png

 

 



 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

Jakinta
Solution Supplier
Solution Supplier

Add this as your next step. Source should be replaced with your previous step/table...

 

 

 

#"Added Custom" = Table.AddColumn(Source, "Task", each if Text.Length([Column])=1
then 
Text.Combine({"Task ",[Column]})
else
Text.Combine({Text.Combine(List.Repeat({" "},Text.Length([Column]))),"Task ",[Column]}))

 

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

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

Top Solution Authors
Top Kudoed Authors