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 Sage
Solution Sage

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! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

Jakinta
Solution Sage
Solution Sage

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 Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors