Question

I have a dataset where column data types change frequently—how can I create a dynamic transformation process?
My dataset contains columns where the data types change unpredictably, leading to errors in Power Query. What strategies can I use to create a flexible transformation process that dynamically adjusts to these changes while maintaining data integrity?

Answer 1

To allow for occasional changes to column data types in Power Query while keeping the transformations stable, dynamic type detection, conditional transformation, and flexible conversion offer three strategies for handling such situations. Consider the following:

1. Detect Data Types and Assign Them Dynamically

Instead of hard-coding column data types, allow the type to be assigned dynamically by calling Table.TransformColumnTypes with Value.Type:

let  
    Source = YourDataSource,  
    ColumnTypes = List.Transform(Table.ColumnNames(Source), each {_, Type.Any}),  
    DynamicTypedTable = Table.TransformColumnTypes(Source, ColumnTypes)  
in  
    DynamicTypedTable

This prevents Power Query from failing when unexpected data types appear, treating all columns as flexible types.

2. Use Conditional Type Handling Based on Column Content

If column types vary but follow a pattern, dynamically determine the type based on content analysis:

let  
    Source = YourDataSource,  
    DetectType = (col) =>  
        if List.MatchesAll(Table.Column(Source, col), each Value.Is(_, type number))  
        then type number  
        else type text,  
    
    ColumnTypes = List.Transform(Table.ColumnNames(Source), each {_, DetectType(_)}),  
    TransformedTable = Table.TransformColumnTypes(Source, ColumnTypes)  
in  
    TransformedTable

This ensures that each column is assigned the correct type based on its actual values.

3. Handle Mixed Data Types Gracefully

If some columns contain mixed data types (e.g., numbers and text), use try…otherwise to clean data without errors:

let  
    Source = YourDataSource,  
    ConvertColumn = (col) => Table.AddColumn(Source, col & "_Fixed", each try Number.From(Record.Field(_, col)) otherwise Text.From(Record.Field(_, col))),  
    ConvertedTable = List.Accumulate(Table.ColumnNames(Source), Source, (acc, col) => ConvertColumn(col))  
in  
    ConvertedTable

This creates a new column that converts numbers where possible but keeps text values intact.