Power Query - Split and Trim Text at the same time

[markhollandau]

Hi,

Is it possible to Split a column by a delimiter AND trim the text at the same time?

I'm using a parameter and list to split a column dynamically, so the name and number of columns can change depending on the parameter and list input. I want to remove any need to manually trim new columns by having Power Query trim all columns that are split.

Here's an example of the Split coding I'm using:

= Table.SplitColumn(Source, "Taxonomy_Campaign", Splitter.SplitTextByDelimiter(#"DV360 Campaign Name - Delimiter", QuoteStyle.Csv), List.Transform({1..List.Count(#"DV360 Campaign Name - Fields")}, each "Campaign Field."&Text.From(_)))

This takes the field "Taxonomy_Campaign" and splits it based on the parameter "DV360 Campaign Name - Delimited". It then splits it into a number of columns, based on the count of list "DV360 Campaign Name - Fields". 

Any pointers would really help.

Thanks,

Mark

[wdx223_Daniel]

= Table.SplitColumn(Source, "Taxonomy_Campaign", each List.Transform(Splitter.SplitTextByDelimiter(#"DV360 Campaign Name - Delimiter", QuoteStyle.Csv)(_),Text.Trim), List.Transform({1..List.Count(#"DV360 Campaign Name - Fields")}, each "Campaign Field."&Text.From(_)))

[markhollandau]

Actually, it would be good if the list of columns could just be a list of Text Columns. That way it can be a bit more dymanic, so I don't need to manually select the metrics fields to exclude.

Do you know how we could do this @JirkaZ ?

[markhollandau]

I've figured it out: if I change your Table.ColumnNames set to Table.ColumnsOfType and make sure I include nullable text, it gives me just the Text columns:

= Table.ColumnsOfType(#"Split Column by Delimiter - Creative", {type nullable text})

Thanks,

Mark

[JirkaZ]

An example code: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsksKFaIKTUwMDJTCCnKTM4uVorViVYKLgExYRLBJfl5qUCJWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MyColumn = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MyColumn", type text}}),
    TableSplit = Table.SplitColumn(#"Changed Type", "MyColumn", Splitter.SplitTextByDelimiter("&", QuoteStyle.Csv)),
    MyColumns = Table.ColumnNames(TableSplit), 
    Transformation = Table.TransformColumns(TableSplit, List.Transform(MyColumns, each{_ , Text.Trim, type text}))
in
    Transformation

[markhollandau · Accepted solution]

Hi @JirkaZ ,

Thanks for this. It works on all the text fields, which is great. I've just used the last 2 steps, as I've amended my split to remove the number at the end of the name and use a list value instead:

#"Split Column by Delimiter - Creative" = Table.SplitColumn( #"Split Column by Delimiter - Line Item", "Taxonomy_Creative", Splitter.SplitTextByDelimiter(#"DV360 Creative - Delimiter", QuoteStyle.Csv), List.Transform(#"DV360 Creative - Fields", each Text.From(_))),
    MyColumns = Table.ColumnNames(#"Split Column by Delimiter - Creative"),
    Transformation = Table.TransformColumns(#"Split Column by Delimiter - Creative", List.Transform(MyColumns, each{_ , Text.Trim, type text})),

But I have 2 date fields and a numerical field in my table. When I add in these steps, these values then show as errors. Is there any way for me to exclude these from the transformation? Or for it to only target text fields?

Thanks,

Mark

[markhollandau]

@JirkaZ  - If I "remove items" to remove the date and numerica fields from the list, then continue on with the transformation step, it looks like it works. Would you agree?

[JirkaZ]

Interesting issue... 

Let's try to come up with a generic approach to that. 
1. The Table.SplitColumn function returns a table
2. We know that the new fields' naming convention is "OriginalColumnName".1, "OriginalColumnName".2 etc.
3. Using Table.ColumnCount we can find out how many new columns there are
4. Using Table.TransformColumns we should be able to perform the Trim (providing a column name and the desired transformation)

J.