The curious case of Column names in Microsoft Fabric Lakehouse

November 29, 2023

In self-service BI tools, having user-friendly column names is crucial for creating an environment that is easy for business users to navigate. The use of whitespace is particularly important in these instances, as it helps maintain the simplicity and user-friendliness of reports and dashboards. While Power BI semantic models and dataflows support column names with spaces, some components in the recently released Microsoft Fabric come with nuances and quirks that must be addressed to ensure business-friendly names can be used effectively in downstream reports and dashboards. In this article, we will explore how to retain the user-friendliness of models while leveraging the best practices of Lakehouse.

Lakehouse

Microsoft Fabric’s Lakehouse is an innovative solution that merges the benefits of data lakes and data warehouses, providing a unified platform for various types of workloads. It uses Delta as its storage format, which in turn relies on Parquet files for storing data. Although Parquet files can technically store column names with whitespace, it is generally discouraged due to compatibility issues. Some components in Fabric may throw errors when column names contain whitespace or other special characters such as brackets, semicolons, or commas.

Dataflows

Dataflow Gen 2 in Fabric represents the next generation of dataflows, introducing a host of new features, including enhanced integration with pipelines for better orchestration capabilities. Organizations are likely to migrate from Power Platform Dataflows to Dataflow Gen 2 because it allows data transformed by citizen developers to be accessible in a Lakehouse environment. This accessibility is particularly beneficial for advanced applications such as data science models and AI use cases. Since both Dataflows use M-query, the migration effort is minimized, while the benefits are substantial.

Handling special characters

When using Power Platform dataflows, it is common to encounter column names that contain whitespace and other special characters. Dataflow Gen 2, when used with a Lakehouse table as the destination, provides an option to convert unsupported characters to underscores (_). If a column contains unsupported characters, it will not be mapped to the Lakehouse data unless the issue is resolved.

One limitation of the default fix is that it replaces all unsupported characters with underscores, without providing the option to customize the replacement character or specify different replacements for specific characters. For example, in the above scenario, Amount (USD) is converted to Amount__USD_. This can complicate the process of automatically converting column names back to their original business-friendly formats.

Custom Power Query function

To address this issue, we have designed a simple M-Query function that replaces unsupported characters with custom-supported characters. The FormatColumns function below takes the name of the source table (Query Name) and the replacement character as arguments. While this function replaces all unsupported characters by default, it can be modified to replace specific characters with specific replacements. For instance, spaces can be replaced with underscores (_), while round brackets can be replaced with pipes (|). This customization requires adjusting the characters within the List.Accumulate function.

// Function FormatColumns: formats column names in a source table
let
    FormatColumns = (SourceTable as table, ReplaceWithCharacter as text) as table =>
    let
        // Custom function that replaces text with replacement text
        ReplaceCharWithCustom = (InputText as text, CharacterToReplace as text) as text =>
            Text.Replace(InputText, CharacterToReplace, ReplaceWithCharacter),

        // Custom function that checks unsupported characters for Delta format and replaces them
        ReplaceForDelta = (InputText as text) as text =>
            List.Accumulate({” “, “[“, “,”, “;”, “{“, “}”, “(“, “)”, “=”, “]”}, InputText, (state, CharacterToReplace) =>
                ReplaceCharWithCustom(state, CharacterToReplace)),

        // Get the original column names and iterate them with ReplaceForDelta function
        OriginalColumnNames = Table.ColumnNames(SourceTable),
        RenamedColumnNames = List.Transform(OriginalColumnNames, each ReplaceForDelta(_)),
        RenamedTable = Table.RenameColumns(SourceTable, List.Zip({OriginalColumnNames, RenamedColumnNames}))

    in
        RenamedTable
in
    FormatColumns

The above code can be added as a function with the name ‘Format Columns’ in the Dataflow Gen2, and it can be used in a query as shown in the GIF image given below.

Conclusion

When consuming data in Power BI from the SQL Endpoint on top of a Lakehouse, the custom M-Query function can be reused with modifications to revert to original column names with special characters. Whitespace and special characters are essential for maintaining user-friendly object names in a business context. The FormatColumns function facilitates the migration of Dataflows without compromising functionality or the user-friendliness of semantic models. This approach ensures that business-friendly naming conventions are preserved, even when working with the technical constraints of Microsoft Fabric.

Govindarajan D

Data Architect

Govindarajan D is a seasoned Data Architect, leveraging expertise in exploring Microsoft technologies. With a passion for innovative solutions, Govindarajan is not only skilled in crafting robust data architecture but also holds the title of Microsoft Certified Trainer (MCT), ensuring cutting-edge knowledge transfer and continuous learning within the tech community.