Multiple data type in the same column

[LukeReds]

Hi to everyone,

i have a table like the one in the image, i need to extract the numbers in 1 column (3,12,11,6,5), the dates in another column and the names in a third column

I was able to extract the names with this line of code, 

= Table.SelectRows(Source, each try if Number.From([dati]) is number then false else true otherwise true )

i'd like to extract the other 2 series of data with the same line, changing of course the parameters.

Thasnk you  in advance

[PhilipTreacy]

Hi @LukeReds 

What do you want as the final result?

This

or this

Is the data in the column related?  You haven't said if it is or not.

This code will give you the 2nd result

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Number", each if [dati] is number then [dati] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Text", each if [dati] is text then [dati] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Date", each if [dati] is number then null else 
try if Date.From([dati]) is date then Date.From([dati]) else null otherwise null),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"Number", Int64.Type}, {"Text", type text}, {"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"dati"})
in
    #"Removed Columns"

Download example Excel file. 

Regards

Phil

[LukeReds]

Hi PhilipTreacy thank you for your answer, i need the first table, the one with 5 rows

I'd like to have 3 lines of code like the one that make me extract the names

 Table.SelectRows(Source, each try if Number.From([dati]) is number then false else true otherwise true )

[LukeReds]

thank you to both of you but i got errors

1) tharunkumarRTK  the error is "its not possible to convert  a table type in a list type"

2) aduguid   with this line of code  try if Date.From([dati]) is date then Date.From([dati]) else null otherwise null

i got this, date and numbers together

  

Thank you again

[Anonymous]

Hi @LukeReds ,

According to the above information, the table [column] should be passed in here, I have made a little modification to the code provided by @tharunkumarRTK , please try and let me know the result.

Advanced editor:

let
    Source ={ "John White" ,
                "Luigi Rossi",
                3,
                12 ,
               #date(2022,11,10),
                #date(2022,10,20) ,
                "Ingemark Sten ma rk" ,
                "Gustav Thoeni" ,
                "Socrates" ,
                11 ,
                6 ,
                9 ,
                #date(2022,11,1) ,
                #date(2023,11,2) ,
                #date(2023,1,2)
                },
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"dati"}, null, ExtraValues.Error),
    ListofDateTypes = {Number.Type, Date.Type, Text.Type},
    SplitTable = Table.FromColumns(List.Accumulate(ListofDateTypes, {}, (s,c)=> s & {List.Select(#"Converted to Table"[dati], each Value.Type(_) = c)}),{"Number","Date","Text"})
in
    SplitTable

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[LukeReds]

hi v-cgao-msft thank you for your answer, yes it functions but you "forced" the date to be date,

#date(2022,11,10),
#date(2022,10,20) ,

in my input table i cant do that, with try may be ita posssible to give different formats to number and date but i was not able to do so

[tharunkumarRTK]

@LukeReds 

will it be possible for you to share the pbix file?

[LukeReds]

hi tharunkumarRTK i'd ike to solve the problem in power query, not in powerBI. With vba it'woudd be quite easy but power query sometimes has an unuseful complex sintax

[tharunkumarRTK]

@LukeReds 

Please find the code:

let
    Source ={ "John White" ,
                "Luigi Rossi",
                3,
                12 ,
               #date(2022,11,10),
                #date(2022,10,20) ,
                "Ingemark Sten ma rk" ,
                "Gustav Thoeni" ,
                "Socrates" ,
                11 ,
                6 ,
                9 ,
                #date(2022,11,1) ,
                #date(2023,11,2) ,
                #date(2023,1,2)
                },
    ListofDateTypes = {Number.Type, Date.Type, Text.Type},
    SplitTable = Table.FromColumns(List.Accumulate(ListofDateTypes, {}, (s,c)=> s & { List.Select(Source, each Value.Type(_) = c)} ),
{"Number","Date","Text"}
)
in
    SplitTable

Need Power BI consultation, hire me on UpWork .

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[aduguid · Accepted solution]

To extract the date you could use this 

if try Date.From([dati]) is date then Date.From([dati]) else null

To extract the numbers you could use this 

if try Number.From([dati]) is number then Number.From([dati]) else null