Forum Discussion

DON_DCS's avatar
DON_DCS
Copper Contributor
Jul 31, 2023

Power Query - Split column by data type

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...

Thank you!

excel
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jul 31, 2023

    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

    • DON_DCS's avatar
      DON_DCS
      Copper Contributor
      Aug 07, 2023

      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

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Aug 07, 2023

        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

Resources