Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi community!I have a column that contains a string in this format:
Type1 Type2 Product "in"Country City
String |
Glas Tube A inFrance Lyon |
Glas Tube B inSpain |
Glas Tube C |
Wood Chair A1 Spain Madrid |
Now, based on the first word in the column I would like to apply an individual split of this column:
How would you apply this kind of logic within power query?
Solved! Go to Solution.
Hi @joshua1990
You could give this a go.
let
ColNames = {"Type1", "Type2", "Product", "Country", "City"},
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs9JLFYIKU1KVXBUyMxzK0rMS05V8KnMz1OK1UGWdQLKBhckZqKLO4P54fn5KQrOGYmZRQqOhgpgdQq+iSlFmSlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [String = _t]),
SplitCol = Table.SplitColumn(Source, "String", Splitter.SplitTextByWhitespace() ),
TrimStart = Table.TransformColumns(
Table.RenameColumns( SplitCol, List.Zip( { Table.ColumnNames( SplitCol ), ColNames })),
{{"Country", each Text.TrimStart(_, {"i", "n"}), type text }}
)
in
TrimStart
With this result
Hello
Here is the magic code
= Table.AddColumn(#"Renamed Columns1", "New", each Text.Select([Name], {"a".."z"}))
We can subsitute Upper and Lower case or number {0..9}.
Thanks
John Haase
Hi @joshua1990
You could give this a go.
let
ColNames = {"Type1", "Type2", "Product", "Country", "City"},
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs9JLFYIKU1KVXBUyMxzK0rMS05V8KnMz1OK1UGWdQLKBhckZqKLO4P54fn5KQrOGYmZRQqOhgpgdQq+iSlFmSlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [String = _t]),
SplitCol = Table.SplitColumn(Source, "String", Splitter.SplitTextByWhitespace() ),
TrimStart = Table.TransformColumns(
Table.RenameColumns( SplitCol, List.Zip( { Table.ColumnNames( SplitCol ), ColNames })),
{{"Country", each Text.TrimStart(_, {"i", "n"}), type text }}
)
in
TrimStart
With this result