Batch conversion of headers and column values

[V-pazhen-msft]

Scenario:

Suppose I have many columns which both column names and fields look untidy.  I want to do some transformations for them in batches. For example, replace multiple spaces as single space and uppercase each first character of word so that the table would look neater.

Sample data:

Expected output:

Guide Line:

To achieve this requirement, we have two main steps:

1. Transform column names and uppercase each first character of word;
2. Transform all column fields and uppercase each first character of word.

Operations:

1. Transform column names: 

1. Extract all column names as a list:  OldColumnNames = Table.ColumnNames(#"Changed Type")
2. Replace multiple spaces into single space: ReplaceSpace = List.Transform(OldColumnNames,each Text.Combine(List.Select(Text.Split(_," "),each _<>"")," "))

1. Uppercase each first character of word: NewColumnNames  = List.Transform(ReplaceSpace, each Text.Proper(_))
2. Apply it to the source table(not for the previous step): RenameColumnName  = Table.RenameColumns(Source,List.Zip({Table.ColumnNames(Source), NewColumnNames}))

2. Transform for all column fields which is similar with step 1:

Since we can transform columns, what about column fields? Actually we can also achieve this.

Usually we create each query for each column transformation, like this:

New1 = Table.TransformColumns(RenameColumnName,{"Column Abcd1", each Text.Combine(List.Select(Text.Split(_," "),each _<>"")," ")})

Upper1 = Table.TransformColumns(New1,{"Column Abcd1",each Text.Proper(_)})

We can get the same result for each column:

 

However, if we have about 50 columns or more, did we need to create queries for each column? Obviously not, not only it wastes much time but also has low performance.

To avoid this phenomenon, we can create custom functions to transform all column fields at once.

1. Create a custom function in the query to replace all multiple spaces at once: ReplaceColumnSpace =  List.Accumulate(NewColumnNames,RenameColumnName,(s,c)=> Table.TransformColumns(s,{c, each Text.Combine(List.Select(Text.Split(_," "),each _<>"")," ")}))
2. Uppercase each first character is similar: NewFields = = List.Accumulate(NewColumnNames,ReplaceColumnSpace,(s,c)=> Table.TransformColumns(s,{c, each Text.Proper(_)}))

Now we could get the expected result just with two queries instead of multiple repeated and similar queries:

 

This is about how we can replace multiple spaces and uppercase each first character. Hope this article helps everyone with similar questions.

Author: Yingjie Li

Reviewer: Ula Huang, Kerry Wang

Sample pbix.pbix