Sort data from single column into multiple columns

[denkersmahlzeit]

Hi community,

I have different data types in one column and want to transform it, so that it is sorted into new columns:

1
Technische Universität München
12.602.708 €
2
Technische Universität Berlin
9.789.161 €
3
Humboldt-Universität Berlin
9.738.703 €
4
Freie Universität Berlin
9.718.436 €
5
Rheinisch-Westfälische Technische Hochschule Aachen (RWTH)
9.424.886 €
6
Universität zu Köln
9.377.422 €
7
Ludwig-Maximilians-Universität München
9.324.297 €
8
Universität Leipzig
9.267.214 €
9

This is a snippet of the one column-table. The index number 1, 2, 3 etc. should be sorted into new column #1, the string values "Technische Universität München" etc. should be sorted into new column #2 and currency values 12.602.708 € etc. should be sorted into column #3.

Complete source table as xlsx can be found here: 2022_DAAD-ausgaben_gesamt_absolut

How can I M-code custom columns for it or solve it with DAX? Thanks a lot for helping me out!

[slorin]

Hi,

= Table.FromRows(List.Split(Your_Source[Your_Column],3), {"#1","#2","#3"})

Stéphane 

[mlsx4]

Hi @denkersmahlzeit 

let
    Origen = Excel.Workbook(File.Contents("c:\2022_DAAD-ausgaben_gesamt_absolut.xlsx"), null, true),
    Tabelle1_Sheet = Origen{[Item="Tabelle1",Kind="Sheet"]}[Data],
    #"Encabezados promovidos" = Table.PromoteHeaders(Tabelle1_Sheet, [PromoteAllScalars=true]),
    #"Filas superiores quitadas" = Table.Skip(#"Encabezados promovidos",2),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Filas superiores quitadas",{{"Rang", type any}}),
    IndexStep = Table.AddIndexColumn(#"Tipo cambiado", "Index", 0, 1, Int64.Type),
    #"Personalizada agregada" = Table.AddColumn(IndexStep, "Personalizado", each try if Number.Mod([Index],3)=0 then IndexStep[Rang]{[Index] } else null otherwise null),
    #"Personalizada agregada1" = Table.AddColumn(#"Personalizada agregada", "Personalizado.1", each try if Number.Mod([Index],3)=0 then IndexStep[Rang]{[Index]+1 } else null otherwise null),
    #"Personalizada agregada2" = Table.AddColumn(#"Personalizada agregada1", "Personalizado.2", each try if Number.Mod([Index],3)=0 then IndexStep[Rang]{[Index] +2} else null otherwise null),
    #"Columnas quitadas" = Table.RemoveColumns(#"Personalizada agregada2",{"Rang", "Index"}),
    #"Filas filtradas" = Table.SelectRows(#"Columnas quitadas", each ([Personalizado] <> null))
in
    #"Filas filtradas"