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

@DON_DCS 

As variant you may return types (as texts) with

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

for such sample it gives

image.png

Thank you @Sergei Baklan 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

@DON_DCS 

As variant

let
    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]  ], {} )
        )[Kind]{0}),
    distinctTypes = Table.Distinct(
        addTypeName, {"TypeName"} ),
    conversion = List.Zip( {distinctTypes[TypeName], distinctTypes[Type]}  ),

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

which returns

image.png