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