Forum Discussion

JD98765's avatar
JD98765
Copper Contributor
Aug 02, 2022
Solved

Split column in excel / power query

  In a column the data is like this: (R = Row ) R1 = number R2 = number R3 = number R4 = text R5 = number R6 = number R7 = text R8 = text R9= number R10 = number So now i want to split th...
excel
  • Harun24HR's avatar
    Harun24HR
    Aug 02, 2022

    JD98765 

    If you can go with Excel formula then can use below one. See the attach file.

    =LET(x,A1:A10,y,FILTER(x,NOT(ISNUMBER(x))),z,FILTER(x,ISNUMBER(x)),IFERROR(CHOOSE({1,2},y,z),""))

     

     

Lorenzo's avatar
Lorenzo
Silver Contributor
Aug 02, 2022

JD98765 

 

With Power Query:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    TableFromColums = Table.FromColumns(
        {
            List.Select(Source[Values], each Type.Is(Value.Type(_), type text)),
            List.Select(Source[Values], each Type.Is(Value.Type(_), type number))
        },
        type table [Text=text, Number=number]
    )
in
    TableFromColums

Resources