Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Does anyone know if it would be possible to use Table.TransformColumnNames to turn something like "IssueIndicatorStatus" into "Issue Indicator Status"
With @mike_honey and @arify help, I have already managed to remove a common string from a bunch of column names with the below, and am wondering if there is a similar method to bulk add spaces.
#"RemovedbwPCI" = Table.TransformColumnNames(#"Removed bwPCache", (columnName as text) as text => Text.Replace(columnName, "bwPCI", "")),
Thanks!
Solved! Go to Solution.
Buried in that thread is my post linking to my demo file for this challenge - it covers your scenario too:
Apply that to your code would be:
#"RemovedbwPCI" = Table.TransformColumnNames(#"Removed bwPCache", each Text.Trim(Text.Proper(Text.Combine( List.Transform( Text.ToList ( _ ) , each if _ = Text.Upper(_) then " " & _ else _ ) , ""))))
Buried in that thread is my post linking to my demo file for this challenge - it covers your scenario too:
Apply that to your code would be:
#"RemovedbwPCI" = Table.TransformColumnNames(#"Removed bwPCache", each Text.Trim(Text.Proper(Text.Combine( List.Transform( Text.ToList ( _ ) , each if _ = Text.Upper(_) then " " & _ else _ ) , ""))))
Hi Mike - thanks again for that. I can't believe I missed that - I actually had your Power Query demo - Renaming Columns automatically open on my desktop.
When I add the column I get Expression.Error: There weren't enough elements in the enumeration to complete the operation.
#"Add Spaces" = Table.TransformColumnNames(#"RemovedbwPCI", each Text.Trim(Text.Proper(Text.Combine( List.Transform( Text.ToList ( _ ) , each if _ = Text.Upper(_) then " " & _ else _ ) , "")))),
@mike_honey I played around with it some more and got it to work.
Thanks an mill for the help - have a great Christmas and I hope you're not sweltering too much.
And BTW - just in case any one else hits the same ExpressionError that I got, my mistake was adding Mike's code in the middle of the steps instead of at the end.
Hi @DonalMc ,
It's possible and @Zubair_Muhammad had a really great solution here . I've made modifications to it to match your criteria:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs7PTfUvyUgtCkmtKFGK1YlWKkkthjCKU9Lc0lKUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each
let
MyList = Text.ToList([Column1]),
ListCount = List.Count(MyList),
ListArr = {"A".."Z"}
in
Text.Combine(List.Generate(()=> [
a = 0,
b = MyList {a}
],
each [a] < ListCount,
each [
a = [a] + 1,
b = if List.Contains(ListArr, MyList {a}) then " " & MyList {a} else MyList {a}
],
each [b]
)))
in
#"Added Custom"
So, pretty much you need to create a new custom column with a function to find the string and add a space in front of it.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |