Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Power Query - Split and Trim Text at the same time

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 cha...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 ?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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