Help convert text to data and convent number to data

Anonymous 

Hi Carlo,

Download an updated PBIX file with code

These transformations are done in the Power Query editor inside Power BI.  You said you imported data from Excel so you must have used Power Query to do this?

In the PQ editor you'll have data like this

It doesn't matter that I'm only using 1 row of data.  The transformations are applied to the whole columns so when you apply the code to your data, all the values in your columns will be changed.

The code below (and in my sample PBIX file) is the result of me manually entering data into Power BI Desktop and then carrying out the transformation steps in Power Query.  It's intended as a demonstration of what you need to do.

The Source step in my code can be replaced by your own Source step - that's the step that loads the data from your Excel file.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjA0MjZU0lEyMTG0sFSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [GIORNO = _t, DT_FINE = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Middle([GIORNO],0,4) & "/" & Text.Middle([GIORNO],4,2) & "/" & Text.Middle([GIORNO],6,2)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Custom", "Date"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"GIORNO"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Date", "GIORNO"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"DT_FINE", Int64.Type}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"DT_FINE", type date}})
in
    #"Changed Type2"

You can either open my PBIX file and copy the query code into your own PBIX file.  Create a new blank query in the PQ editor and paste in the code. Or copy/paste the code above.

If you already have a query with other transformaion steps then my cdoe will have to be integrated into yours.  If you can't get that part working you'll need to copy/paste your query code in here so I can do that for you.  Or link to your PBIX file so I can download it and do the same.

Regards

Phil