Power Query Trim All Table Dynamically

[chleidersdorff]

I have a bunch of text files with columnar format but with 90% same data but there are differences. I have done a bunch of steps on them applying dynamic transformations when appropriate because they are not all the same data. The final step is to clean all the whitespaces (Trim) but I am not sure how i can apply the Text.Trim function dynamically to all data?



Any help much appreciated

 

[sandy666]

select all columns and use Trim

 

[chleidersdorff]

thanks for the suggestion sandy666 however i am using this in a custom function against files that have variable number of columns and names. therefore i need something that doesn't rely on me opening every file and select all columns... Hence the title "Dynamic".

 

[sandy666]

I don't know structure of your project so how do I know what are you trying to do?
anyway I can't help without knowing whole structure and M
Have a nice day ?

 

[chleidersdorff]

this is the PQ M code that i have so far I just need to TRIM all columns from tables:

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("T:\Christian Leidersdorff\Daily Drilling Reports\3.0 Assays Received\S02\MI20052724.S02"), null, null, 1252)}),
    ColRemTop = Table.Skip(Source,13),
    ColRemBot = Table.FirstN(ColRemTop,7),
    Col1 = Table.SplitColumn(ColRemBot, "Column1", Splitter.SplitTextByPositions({0, 25}, false), {"Column1.1", "Column1.2"}),
    Col2 = Table.SplitColumn(Col1, "Column1.2", Splitter.SplitTextByPositions({0, 20}, false), {"Column1.2.1", "Column1.2.2"}),
    ColFilter = Table.SelectRows(Col2, each ([Column1.1] = "meth                     " or [Column1.1] = "sampno                   " or [Column1.1] = "uom                      ")),
    ColAllCol = Table.SplitColumn(ColFilter, "Column1.2.2", Splitter.SplitTextByRepeatedLengths(20), {"Column1.2.2.1", "Column1.2.2.2", "Column1.2.2.3", "Column1.2.2.4", "Column1.2.2.5", "Column1.2.2.6", "Column1.2.2.7", "Column1.2.2.8", "Column1.2.2.9", "Column1.2.2.10", "Column1.2.2.11", "Column1.2.2.12", "Column1.2.2.13", "Column1.2.2.14", "Column1.2.2.15", "Column1.2.2.16", "Column1.2.2.17", "Column1.2.2.18", "Column1.2.2.19", "Column1.2.2.20", "Column1.2.2.21", "Column1.2.2.22", "Column1.2.2.23", "Column1.2.2.24", "Column1.2.2.25", "Column1.2.2.26", "Column1.2.2.27", "Column1.2.2.28", "Column1.2.2.29", "Column1.2.2.30", "Column1.2.2.31", "Column1.2.2.32", "Column1.2.2.33", "Column1.2.2.34", "Column1.2.2.35", "Column1.2.2.36", "Column1.2.2.37", "Column1.2.2.38", "Column1.2.2.39", "Column1.2.2.40", "Column1.2.2.41", "Column1.2.2.42", "Column1.2.2.43", "Column1.2.2.44", "Column1.2.2.45", "Column1.2.2.46", "Column1.2.2.47", "Column1.2.2.48", "Column1.2.2.49", "Column1.2.2.50", "Column1.2.2.51", "Column1.2.2.52", "Column1.2.2.53", "Column1.2.2.54", "Column1.2.2.55", "Column1.2.2.56", "Column1.2.2.57", "Column1.2.2.58", "Column1.2.2.59", "Column1.2.2.60", "Column1.2.2.61", "Column1.2.2.62", "Column1.2.2.63", "Column1.2.2.64", "Column1.2.2.65"}),
    ColDemoteHeader = Table.DemoteHeaders(ColAllCol),
    ColTranspose = Table.Transpose(ColDemoteHeader),
    ColTrim = Table.TransformColumns(ColTranspose,{{"Column2", Text.Trim, type text}, {"Column3", Text.Trim, type text}, {"Column4", Text.Trim, type text}}),
    ColPctreplace = Table.ReplaceValue(ColTrim,"%","pct",Replacer.ReplaceText,{"Column3"}),
    ColNULL = Table.ReplaceValue(ColPctreplace,"",null,Replacer.ReplaceValue,{"Column2", "Column3", "Column4"}),
    ColMerge = Table.CombineColumns(ColNULL,{"Column4", "Column2", "Column3"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Names"),
    FinalNewColumnNames = Table.ReplaceValue(ColMerge,"__","",Replacer.ReplaceText,{"Names"}),
    #"Removed Top Rows" = Table.Skip(Source,20),
    #"Split Column by Position" = Table.SplitColumn(#"Removed Top Rows", "Column1", Splitter.SplitTextByPositions({0, 40}, false), {"Column1.1", "Column1.2"}),
    #"Split Column by Position1" = Table.SplitColumn(#"Split Column by Position", "Column1.2", Splitter.SplitTextByPositions({0, 17}, false), {"Column1.2.1", "Column1.2.2"}),
    #"Split Column by Position2" = Table.SplitColumn(#"Split Column by Position1", "Column1.2.2", Splitter.SplitTextByRepeatedLengths(20), {"Column1.2.2.1", "Column1.2.2.2", "Column1.2.2.3", "Column1.2.2.4", "Column1.2.2.5", "Column1.2.2.6", "Column1.2.2.7", "Column1.2.2.8", "Column1.2.2.9", "Column1.2.2.10", "Column1.2.2.11", "Column1.2.2.12", "Column1.2.2.13", "Column1.2.2.14", "Column1.2.2.15", "Column1.2.2.16", "Column1.2.2.17", "Column1.2.2.18", "Column1.2.2.19", "Column1.2.2.20", "Column1.2.2.21", "Column1.2.2.22", "Column1.2.2.23", "Column1.2.2.24", "Column1.2.2.25", "Column1.2.2.26", "Column1.2.2.27", "Column1.2.2.28", "Column1.2.2.29", "Column1.2.2.30", "Column1.2.2.31", "Column1.2.2.32", "Column1.2.2.33", "Column1.2.2.34", "Column1.2.2.35", "Column1.2.2.36", "Column1.2.2.37", "Column1.2.2.38", "Column1.2.2.39", "Column1.2.2.40", "Column1.2.2.41", "Column1.2.2.42", "Column1.2.2.43", "Column1.2.2.44", "Column1.2.2.45", "Column1.2.2.46", "Column1.2.2.47", "Column1.2.2.48", "Column1.2.2.49", "Column1.2.2.50", "Column1.2.2.51", "Column1.2.2.52", "Column1.2.2.53", "Column1.2.2.54", "Column1.2.2.55", "Column1.2.2.56", "Column1.2.2.57", "Column1.2.2.58", "Column1.2.2.59", "Column1.2.2.60", "Column1.2.2.61", "Column1.2.2.62", "Column1.2.2.63", "Column1.2.2.64", "Column1.2.2.65"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Position2",Table.ToRows(FinalNewColumnNames))
in
    #"Renamed Columns"

 

[sandy666]

so why not on the end
Trim = Table.TransformColumns(Table.TransformColumnTypes(#"Renamed Columns", {.... all columns
use this after each step where can be additional space (usually Split)

 

[sandy666]

or you can play with
Table.SelectColumns(last_step,Table.Schema(last_step)[Name]) and you've selected all columns
then Text.Trim()
and Table.SelectColumns()