Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Help convert text to data and convent number to data

hi,   i'm new here and this is my first post.   i imported from excel a db, but the format is not correct, and try to change a format but without success.   i have 2 problems with 2 different c...
Need Help
PhilipTreacy's avatar
PhilipTreacy
Icon for Super User rankSuper User
5 years ago

Hi Anonymous 

Download sample PBIX file

To convert the GIORNO column use this code in Power Query

 

 

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"}})
in
    #"Renamed Columns1"

 

 

As for the DT_FINE column, that looks like an Excel date serial number - for 24 Dec 2020.  Can't you convert that in Excel to a date before you bring it into PBI?  That would be easier than trying to to the conversion once you get the data into PBI.

regards

Phil