Power Query to Promote Headers and removing Special Characters
I had an Excel spreadsheet that had "nicely" formatted column headers that made the text appear on separate lines (ie. the text included Alt-Enter linefeeds). I wanted to use it as a source for Power Query (and PowerBI), but remove the linefeeds in the names before promoting them to the column header.
I found a post on MSDN forums that did something similar:
In the thread, Ehren posted a snippet that combines the first four lines of the source to become the column header names:
= let
firstN = Table.FirstN(Source, 4),
renames = List.Transform(
Table.ColumnNames(Source),
each {_, Text.Combine(List.Transform(Table.Column(firstN, _), each Text.From(_)), ",")}),
renamedTable = Table.RenameColumns(Source, renames)
in
Table.Skip(renamedTable, 4)
I modified this slightly to only use the first line, but remove the linefeeds using the special token "#{lf}"
= let
firstN = Table.FirstN(Source, 1),
renames = List.Transform(
Table.ColumnNames(Source),
each {_, Text.Combine(
List.Transform(Table.Column(firstN, _), each Text.Trim(Text.Replace(Text.From(_),"#(lf)"," "))
),"")}),
renamedTable = Table.RenameColumns(Source, renames)
in
Table.Skip(renamedTable, 1)
Ehren's original instructions still apply to using this formula:
Click the little fx button to add a custom step to your query, and paste this in. It handles doing the promotion for all columns in the table without the need for hard-coding. (It assumes the previous step in the query is called Source. Please update it accordingly if that's not the case.)
Mike