Beautify Power BI column names from NAV's OData - function to replace underscores

ERP-Systems and BI solutions have it in common that columns should be as self-explanatory as possible. Power BI currently does not provide a way to create something like captions for fields. In contrast to NAV with multi language captions and SQL Server Analysis Services with translations a column in Power BI is only defined by its name. The column name in Power BI will be derived from the field Name of the page or query whenever the OData-feed is sourced by NAV:

As you may have experienced yourself or seen in the previous screenshot, Power BI (both Power BI desktop and Power Query in Excel) will replace some characters such as spaces with underscores (_). This is actually in conformity with the OData specification - however column names with underscores are not best with regards to readability. This is especially problematic if the Q&A (link) functionality is used since intuitive column names are the main requirement for a working Q&A-engine (side-note: Alias that can be defined on Power Bi for Excel may help, right now they are however not available in Power BI Desktop).

It would be possible to manually go through all columns and to change the underscores back to spaces. This would be tedious and error-prone for bigger tables. Fortunately Power Query can help to automate this process.

The idea is to transform the header row to a “normal” column where a text replace can happen.

First we need to “demote” the header in order to bring the real column names to the first (non-header) row:

Now we only want to keep this first row…

…in order to replace _ with blanks in all columns:

In the next step the rows from my date-table need to be appended to the improved header row:

Finally we need to reverse the DemoteHeader step – the function is called PromoteHeaders. This will bring the real header back:

To allow reusing these steps for any OData-feed, it makes sense to create a function that takes a table as input and returns the same table with beautified column names:

let RemoveUnderScoreFunction= (inputTable as table) =>

letSource = inputTable,

#"Demoted Headers" = Table.DemoteHeaders(Source),

#"Kept First Rows" = Table.FirstN(#"Demoted Headers",1),

#"All but First Row" = Table.Skip(#"Demoted Headers",1),

#"Replaced Value" = Table.ReplaceValue(#"Kept First Rows","_"," ",Replacer.ReplaceText,Table.ColumnNames(#"All but First Row")),

#"Appended Query" = Table.Combine({#"Replaced Value", #"All but First Row"}),Result = Table.PromoteHeaders(#"Appended Query")inResultinRemoveUnderScoreFunction

Using this function for example for my date-table would look like this:

let

Source = OData.Feed("http://NAVIDA-SERVER:7348/NAV_80_Extended_UserPassword/OData/Company('CRONUS%20EXT%20AG')/NBI_DateTable_ENU"),
Result = RemoveUnderScores(Source)

in

Result

Thanks to the definition of the #”Replaced Value” transformation step to replace underscores in all columns, the function will also (automatically) adapt to changed OData-feeds, for example if a field is added to an existing page.