Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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]}))
@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.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Both solutions worked, thank you so much 🙂
@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.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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]}))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |