Power Query - Split column by data type

Copper Contributor

Hi all, could anyone please help with M language to show how to split a mixed data type column (Attribute) to its relevant data type columns, the column names can be a dynamic list (Dynamic Col) to provide flexibility for further data type like Date...

Split column by data type.JPG

Thank you!

3 Replies


As variant you may return types (as texts) with

  addType = Table.AddColumn(
        each Table.Schema(
            #table(type table [a =  Value.Type( _[Attribut])  ], {} )
        , type text),

for such sample it gives


Thank you @SergeiBaklan for showing the Table.Schema and a great way to expand the wanted column in one go. It would be great if you could show the next step from here how to split the Attribute into respective type columns with dynamic column names, i.e. 333 to Number col, John to Text col and so on


As variant

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    addType = Table.AddColumn(Source, "Type", each Value.Type([Attribut])),
    addTypeName = Table.AddColumn(addType, "TypeName", each Table.Schema(
            #table(type table [a =   _[Type]  ], {} )
    distinctTypes = Table.Distinct(
        addTypeName, {"TypeName"} ),
    conversion = List.Zip( {distinctTypes[TypeName], distinctTypes[Type]}  ),

    names = distinctTypes[TypeName],
    values = List.Transform(
        (q) => Table.SelectRows(addTypeName, each ([TypeName] = q))[Attribut] ),
    combineTable = Table.FromColumns(values,names),
    declareTypes = Table.TransformColumnTypes(combineTable,conversion)

which returns