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.
Hi all,
Imagine we have a column in which we have different fields separated by an arbitrary number of spaces. Example:
Column
"1 Red 23 Yellow"
and we want to use the Query Editor to split the above in four columns:
C1 C2 C3 C4
1 Red 23 Yellow
i.e., the delimiter is "1 or more spaces".
This would be easy if the Trim function in M worked as the one in Excel but it only seems to remove the trailing spaces, not the ones between words.
How can this be done in M/Query Editor?
Many thanks
Solved! Go to Solution.
One way to do this is to split the text into a list based on the delimiter, remove nulls, and then recombine the list into a string with just a single space between words.
= Table.TransformColumns(Source, {{"Column1", each Text.Combine(List.Select(Text.SplitAny(_, " "), each _ <> "")," "), type text}})
or in expanded format
= Table.TransformColumns(
Source,
{{"Column1",
each Text.Combine(
List.Select(
Text.SplitAny(_, " "),
each _ <> ""
)
," "
),
type text
}}
)
Then you can split this transformed column by the space delimiter.
One way to do this is to split the text into a list based on the delimiter, remove nulls, and then recombine the list into a string with just a single space between words.
= Table.TransformColumns(Source, {{"Column1", each Text.Combine(List.Select(Text.SplitAny(_, " "), each _ <> "")," "), type text}})
or in expanded format
= Table.TransformColumns(
Source,
{{"Column1",
each Text.Combine(
List.Select(
Text.SplitAny(_, " "),
each _ <> ""
)
," "
),
type text
}}
)
Then you can split this transformed column by the space delimiter.
this solution worked great for my same issue. Thanks for contributing!
Thanks a lot @AlexisOlson. It works.
I still have to take a good look to understand well what the code does. I'm not very familiar with M. Might get back to you with some question.
In any case, I was surprised M does not have a function that does this directly, like excel.
Here's what the logic does:
If you have String = "1 Red 23 Yellow", then Text.SplitAny(String, " ") is the list:
{"1","","","","","","","","Red","23","","","","","","","","","","","","","","","","","","","","","","","","Yellow"}
Using List.Select to choose only elements that are not empty strings, "", you get:
{"1","Red","23","Yellow"}
Combining that list back into a string with Text.Combine gives you the final result:
"1 Red 23 Yellow"
Two additional questions:
1. For your solution, is it necessary to write the code directly in M (in the editor) or can this be done by using a combination of the steps in the menus?
2. Since you seem well versed in M, do you know how to change the value of a single cell in a table in the query editor
Thanks very much for your time
@AlexisOlson wrote:
2.There are some awkward ways to transform individual cells, but I'd recommend only doing that as a last resort if you can't find a better method to process your data.
I agree it's not a good approach in general but I just want it to change values quickly and directly in Power BI when I am testing something with dummy data. Could you show how to do it? Thanks
One way would be to create a custom column where you swap out the value based on the row number. This assumes you have an index column, but if you don't it's easy to add.
= Table.AddColumn(#"Previous Step", "Custom", each if [Index] = <row number> then <value> else [SourceColumn])
You can either use that column instead or delete the source column and rename the custom column to match the source columns name.
That's great. Thanks very much @AlexisOlson
Is there some alternative faster than that you can think of? I'd just want to avoid the renaming of the columns if possible.
Many thanks for your patience
I think you should be able to do a column transform on the column you want to change instead of creating a new one and then renaming, but I was having trouble getting the syntax right when I tried that instead.
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |