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,
What is the good M code to break this text column into two columns?
I was experimenting with Splitter functions but no luck....
Thanks
Solved! Go to Solution.
You've got a couple of options. If you can guarantee a certain amount of spaces both leading and between your numbers, you can split by number of characters. For example, if there are 8 spaces, a number, and then 8 more spaces, you could split on position 14, assuming your number is never more than 6 digits long.
See screenshot:
Otherwise, you can first trim the column, then split on the first space (leftmost), then an auto-type-detect will get your your numbers:
It all depends on the possible formats of your data.
Example in he following code. Note: after splitting the column, a "Changed Tyoe" step was generated, turning the data into numbers; I removed this step. On second thought, that might also be a possibility to change the numbers back to text. Then the spaces will be gone as well.
let Source = #table(1,{{" 3 0 "},{" 2 0 "}}), #"Split Column by Position" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 10}, false), {"Column1.1", "Column1.2"}), #"Trimmed Text" = Table.TransformColumns(#"Split Column by Position",{{"Column1.1", Text.Trim, type text}, {"Column1.2", Text.Trim, type text}}) in #"Trimmed Text"
You've got a couple of options. If you can guarantee a certain amount of spaces both leading and between your numbers, you can split by number of characters. For example, if there are 8 spaces, a number, and then 8 more spaces, you could split on position 14, assuming your number is never more than 6 digits long.
See screenshot:
Otherwise, you can first trim the column, then split on the first space (leftmost), then an auto-type-detect will get your your numbers:
So it looks like you have a bunch of leading, middle and trailing spaces.
Maybe you can split on position and trim the resulting columns?
My idea was to extract first character in the first separate column and then second into a second column.
Or delete leading and trailing spaces, split the by the 1st space and then clean all the spaces
Does this sound like a good strategy ?
Thanks
It all depends on the possible formats of your data.
Example in he following code. Note: after splitting the column, a "Changed Tyoe" step was generated, turning the data into numbers; I removed this step. On second thought, that might also be a possibility to change the numbers back to text. Then the spaces will be gone as well.
let Source = #table(1,{{" 3 0 "},{" 2 0 "}}), #"Split Column by Position" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 10}, false), {"Column1.1", "Column1.2"}), #"Trimmed Text" = Table.TransformColumns(#"Split Column by Position",{{"Column1.1", Text.Trim, type text}, {"Column1.2", Text.Trim, type text}}) in #"Trimmed Text"
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |