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 column: column called GIORNO is in text format 20201231. how is it posible to convert in data (DD/AA/YY) ? 2 column called DT_FINE is in number format 44189. how is it posible to convert in data (DD/AA/YY) ? i'd like one coluns converted for each data. thanks Carlo

Thanks for the reply. Could you please tell me the correct path on where to put your answer. I would like for each row of the DB the transformed date and not a single row as in your example. for DT_FINE I cannot modify the excel. thanks for your patience.

thanks for your patience and for the answers, but when i enter the text in power query i have an error. I simplified the db, to have only the DAY column (20190901 in text format) to convert Here is the mistake I have. Maybe I was wrong to follow the directions? my table is called DB_GLOBALE this is the global_db query without your suggestionsletOrigine = Excel.Workbook(File.Contents("C:\profili\U399105\Desktop\POWER BI\V1\01_DB_GLOBALE.xlsx"), null, true),#"01_DB_GLOBALE_Sheet" = Origine{[Item="01_DB_GLOBALE",Kind="Sheet"]}[Data],#"Intestazioni alzate di livello" = Table.PromoteHeaders(#"01_DB_GLOBALE_Sheet", [PromoteAllScalars=true]),#"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"ChiaveGestore", type text}, {"Gestore", Int64.Type}, {"Giorno", Int64.Type}, {"Banca", Int64.Type}, {"COD_PTF", type text}, {"TIPO_PTF", type text}, {"CodProd", type text}, {"Report", type text}, {"Prodotto", type text}, {"SaldoC", type number}, {"MeseC", type number}, {"AnnoC", type number}, {"TOT_COLLOC", type number}, {"RG_CERT", type number}, {"ClientiGestore", Int64.Type}, {"ORG_SUPERAREA_UO", Int64.Type}, {"CML", type number}, {"NumNdgC", Int64.Type}, {"MINTC", type number}, {"MINTC_upfront", type number}, {"MINTC_Mant", type number}, {"MeseRpc", Int64.Type}, {"NUM_PTF", Int64.Type}, {"COORD", type text}}),#"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "DT_GG", each Date.FromText([Giorno],"GG MM AAAA")),#"Modificato tipo1" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata",{{"DT_GG", type date}}),#"Rimosse colonne" = Table.RemoveColumns(#"Modificato tipo1",{"DT_GG"}),#"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Rimosse colonne", "AAAA", each [Giorno]),#"Caratteri iniziali inseriti" = Table.AddColumn(#"Aggiunta colonna personalizzata1", "Caratteri iniziali", each Text.Start(Text.From([AAAA], "it-IT"), 4), type text),#"Intervallo testo inserito" = Table.AddColumn(#"Caratteri iniziali inseriti", "Intervallo testo", each Text.Middle(Text.From([AAAA], "it-IT"), 4, 2), type text),#"Caratteri finali inseriti" = Table.AddColumn(#"Intervallo testo inserito", "Caratteri finali", each Text.End(Text.From([AAAA], "it-IT"), 2), type text),#"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Caratteri finali inseriti", "GGG", each [Caratteri finali]& "-" &[Intervallo testo]& "-" &[Caratteri iniziali]),#"Modificato tipo2" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata2",{{"GGG", type date}}),#"Rinominate colonne" = Table.RenameColumns(#"Modificato tipo2",{{"GGG", "DATA_CORRETTA"}}),#"Modificato tipo3" = Table.TransformColumnTypes(#"Rinominate colonne",{{"DATA_CORRETTA", type date}}),#"Rimosse colonne1" = Table.RemoveColumns(#"Modificato tipo3",{"Intervallo testo", "Caratteri finali", "Caratteri iniziali", "AAAA"})in#"Rimosse colonne1"and this is how I tried to put your suggestions. Maybe am I wrong here?letOrigine = Excel.Workbook(File.Contents("C:\profili\U399105\Desktop\POWER BI\V1\01_DB_GLOBALE.xlsx"), null, true),#"01_DB_GLOBALE_Sheet" = Origine{[Item="01_DB_GLOBALE",Kind="Sheet"]}[Data],#"Intestazioni alzate di livello" = Table.PromoteHeaders(#"01_DB_GLOBALE_Sheet", [PromoteAllScalars=true]),#"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"ChiaveGestore", type text}, {"Gestore", Int64.Type}, {"Giorno", Int64.Type}, {"Banca", Int64.Type}, {"COD_PTF", type text}, {"TIPO_PTF", type text}, {"CodProd", type text}, {"Report", type text}, {"Prodotto", type text}, {"SaldoC", type number}, {"MeseC", type number}, {"AnnoC", type number}, {"TOT_COLLOC", type number}, {"RG_CERT", type number}, {"ClientiGestore", Int64.Type}, {"ORG_SUPERAREA_UO", Int64.Type}, {"CML", type number}, {"NumNdgC", Int64.Type}, {"MINTC", type number}, {"MINTC_upfront", type number}, {"MINTC_Mant", type number}, {"MeseRpc", Int64.Type}, {"NUM_PTF", Int64.Type}, {"COORD", type text}}),#"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "DT_GG", each Date.FromText([Giorno],"GG MM AAAA")),#"Modificato tipo1" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata",{{"DT_GG", type date}}),#"Rimosse colonne" = Table.RemoveColumns(#"Modificato tipo1",{"DT_GG"}),#"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Rimosse colonne", "AAAA", each [Giorno]),#"Caratteri iniziali inseriti" = Table.AddColumn(#"Aggiunta colonna personalizzata1", "Caratteri iniziali", each Text.Start(Text.From([AAAA], "it-IT"), 4), type text),#"Intervallo testo inserito" = Table.AddColumn(#"Caratteri iniziali inseriti", "Intervallo testo", each Text.Middle(Text.From([AAAA], "it-IT"), 4, 2), type text),#"Caratteri finali inseriti" = Table.AddColumn(#"Intervallo testo inserito", "Caratteri finali", each Text.End(Text.From([AAAA], "it-IT"), 2), type text),#"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Caratteri finali inseriti", "GGG", each [Caratteri finali]& "-" &[Intervallo testo]& "-" &[Caratteri iniziali]),#"Modificato tipo2" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata2",{{"GGG", type date}}),#"Rinominate colonne" = Table.RenameColumns(#"Modificato tipo2",{{"GGG", "DATA_CORRETTA"}}),#"Modificato tipo3" = Table.TransformColumnTypes(#"Rinominate colonne",{{"DATA_CORRETTA", type date}}),#"Rimosse colonne1" = Table.RemoveColumns(#"Modificato tipo3",{"Intervallo testo", "Caratteri finali", "Caratteri iniziali", "AAAA"})letOrigine = Excel.Workbook(File.Contents("C:\profili\U399105\Desktop\POWER BI\V1\01_DB_GLOBALE.xlsx"), 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"#"Rimosse colonne1"

Help convert text to data and convent number to data

12 Replies

PhilipTreacy

Super 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

Anonymous
Not applicable
5 years ago
Hi PhilipTreacy , unfortunately it doesn't work. Here is my PB file. The table is called GLOBAL_DB

prova_v4

thanks

Anonymous
Not applicable
5 years ago
Thanks for the reply. Could you please tell me the correct path on where to put your answer. I would like for each row of the DB the transformed date and not a single row as in your example. for DT_FINE I cannot modify the excel. thanks for your patience.
- PhilipTreacy
  Super User
  5 years ago
  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

Ashish_Mathur

Super User

5 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"GIORNO", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.From(Text.From([GIORNO], "en-US")))
in
    #"Added Custom"

Hope this helps.

Anonymous
Not applicable
5 years ago
thanks for your patience and for the answers, but when i enter the text in power query i have an error. I simplified the db, to have only the DAY column (20190901 in text format) to convert Here is the mistake I have. Maybe I was wrong to follow the directions? my table is called DB_GLOBALE this is the global_db query without your suggestions

let
Origine = Excel.Workbook(File.Contents("C:\profili\U399105\Desktop\POWER BI\V1\01_DB_GLOBALE.xlsx"), null, true),
#"01_DB_GLOBALE_Sheet" = Origine{[Item="01_DB_GLOBALE",Kind="Sheet"]}[Data],
#"Intestazioni alzate di livello" = Table.PromoteHeaders(#"01_DB_GLOBALE_Sheet", [PromoteAllScalars=true]),
#"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"ChiaveGestore", type text}, {"Gestore", Int64.Type}, {"Giorno", Int64.Type}, {"Banca", Int64.Type}, {"COD_PTF", type text}, {"TIPO_PTF", type text}, {"CodProd", type text}, {"Report", type text}, {"Prodotto", type text}, {"SaldoC", type number}, {"MeseC", type number}, {"AnnoC", type number}, {"TOT_COLLOC", type number}, {"RG_CERT", type number}, {"ClientiGestore", Int64.Type}, {"ORG_SUPERAREA_UO", Int64.Type}, {"CML", type number}, {"NumNdgC", Int64.Type}, {"MINTC", type number}, {"MINTC_upfront", type number}, {"MINTC_Mant", type number}, {"MeseRpc", Int64.Type}, {"NUM_PTF", Int64.Type}, {"COORD", type text}}),
#"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "DT_GG", each Date.FromText([Giorno],"GG MM AAAA")),
#"Modificato tipo1" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata",{{"DT_GG", type date}}),
#"Rimosse colonne" = Table.RemoveColumns(#"Modificato tipo1",{"DT_GG"}),
#"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Rimosse colonne", "AAAA", each [Giorno]),
#"Caratteri iniziali inseriti" = Table.AddColumn(#"Aggiunta colonna personalizzata1", "Caratteri iniziali", each Text.Start(Text.From([AAAA], "it-IT"), 4), type text),
#"Intervallo testo inserito" = Table.AddColumn(#"Caratteri iniziali inseriti", "Intervallo testo", each Text.Middle(Text.From([AAAA], "it-IT"), 4, 2), type text),
#"Caratteri finali inseriti" = Table.AddColumn(#"Intervallo testo inserito", "Caratteri finali", each Text.End(Text.From([AAAA], "it-IT"), 2), type text),
#"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Caratteri finali inseriti", "GGG", each [Caratteri finali]& "-" &[Intervallo testo]& "-" &[Caratteri iniziali]),
#"Modificato tipo2" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata2",{{"GGG", type date}}),
#"Rinominate colonne" = Table.RenameColumns(#"Modificato tipo2",{{"GGG", "DATA_CORRETTA"}}),
#"Modificato tipo3" = Table.TransformColumnTypes(#"Rinominate colonne",{{"DATA_CORRETTA", type date}}),
#"Rimosse colonne1" = Table.RemoveColumns(#"Modificato tipo3",{"Intervallo testo", "Caratteri finali", "Caratteri iniziali", "AAAA"})
in
#"Rimosse colonne1"

and this is how I tried to put your suggestions. Maybe am I wrong here?

let
Origine = Excel.Workbook(File.Contents("C:\profili\U399105\Desktop\POWER BI\V1\01_DB_GLOBALE.xlsx"), null, true),
#"01_DB_GLOBALE_Sheet" = Origine{[Item="01_DB_GLOBALE",Kind="Sheet"]}[Data],
#"Intestazioni alzate di livello" = Table.PromoteHeaders(#"01_DB_GLOBALE_Sheet", [PromoteAllScalars=true]),
#"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"ChiaveGestore", type text}, {"Gestore", Int64.Type}, {"Giorno", Int64.Type}, {"Banca", Int64.Type}, {"COD_PTF", type text}, {"TIPO_PTF", type text}, {"CodProd", type text}, {"Report", type text}, {"Prodotto", type text}, {"SaldoC", type number}, {"MeseC", type number}, {"AnnoC", type number}, {"TOT_COLLOC", type number}, {"RG_CERT", type number}, {"ClientiGestore", Int64.Type}, {"ORG_SUPERAREA_UO", Int64.Type}, {"CML", type number}, {"NumNdgC", Int64.Type}, {"MINTC", type number}, {"MINTC_upfront", type number}, {"MINTC_Mant", type number}, {"MeseRpc", Int64.Type}, {"NUM_PTF", Int64.Type}, {"COORD", type text}}),
#"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "DT_GG", each Date.FromText([Giorno],"GG MM AAAA")),
#"Modificato tipo1" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata",{{"DT_GG", type date}}),
#"Rimosse colonne" = Table.RemoveColumns(#"Modificato tipo1",{"DT_GG"}),
#"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Rimosse colonne", "AAAA", each [Giorno]),
#"Caratteri iniziali inseriti" = Table.AddColumn(#"Aggiunta colonna personalizzata1", "Caratteri iniziali", each Text.Start(Text.From([AAAA], "it-IT"), 4), type text),
#"Intervallo testo inserito" = Table.AddColumn(#"Caratteri iniziali inseriti", "Intervallo testo", each Text.Middle(Text.From([AAAA], "it-IT"), 4, 2), type text),
#"Caratteri finali inseriti" = Table.AddColumn(#"Intervallo testo inserito", "Caratteri finali", each Text.End(Text.From([AAAA], "it-IT"), 2), type text),
#"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Caratteri finali inseriti", "GGG", each [Caratteri finali]& "-" &[Intervallo testo]& "-" &[Caratteri iniziali]),
#"Modificato tipo2" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata2",{{"GGG", type date}}),
#"Rinominate colonne" = Table.RenameColumns(#"Modificato tipo2",{{"GGG", "DATA_CORRETTA"}}),
#"Modificato tipo3" = Table.TransformColumnTypes(#"Rinominate colonne",{{"DATA_CORRETTA", type date}}),
#"Rimosse colonne1" = Table.RemoveColumns(#"Modificato tipo3",{"Intervallo testo", "Caratteri finali", "Caratteri iniziali", "AAAA"})

let
Origine = Excel.Workbook(File.Contents("C:\profili\U399105\Desktop\POWER BI\V1\01_DB_GLOBALE.xlsx"), 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"
#"Rimosse colonne1"
- PhilipTreacy
  Super User
  5 years ago
  Hi Anonymous
  
  Thanks for this but without seeing your data it makes it more difficult.
  
  Please try this, it requires that there is a column called GIORNO and another column called DT_FINE
  
  let Origine = Excel.Workbook(File.Contents("C:\profili\U399105\Desktop\POWER BI\V1\01_DB_GLOBALE.xlsx"), null, true), #"01_DB_GLOBALE_Sheet" = Origine{[Item="01_DB_GLOBALE",Kind="Sheet"]}[Data], #"Intestazioni alzate di livello" = Table.PromoteHeaders(#"01_DB_GLOBALE_Sheet", [PromoteAllScalars=true]), #"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"ChiaveGestore", type text}, {"Gestore", Int64.Type}, {"Giorno", Int64.Type}, {"Banca", Int64.Type}, {"COD_PTF", type text}, {"TIPO_PTF", type text}, {"CodProd", type text}, {"Report", type text}, {"Prodotto", type text}, {"SaldoC", type number}, {"MeseC", type number}, {"AnnoC", type number}, {"TOT_COLLOC", type number}, {"RG_CERT", type number}, {"ClientiGestore", Int64.Type}, {"ORG_SUPERAREA_UO", Int64.Type}, {"CML", type number}, {"NumNdgC", Int64.Type}, {"MINTC", type number}, {"MINTC_upfront", type number}, {"MINTC_Mant", type number}, {"MeseRpc", Int64.Type}, {"NUM_PTF", Int64.Type}, {"COORD", type text}}), #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "DT_GG", each Date.FromText([Giorno],"GG MM AAAA")), #"Modificato tipo1" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata",{{"DT_GG", type date}}), #"Rimosse colonne" = Table.RemoveColumns(#"Modificato tipo1",{"DT_GG"}), #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Rimosse colonne", "AAAA", each [Giorno]), #"Caratteri iniziali inseriti" = Table.AddColumn(#"Aggiunta colonna personalizzata1", "Caratteri iniziali", each Text.Start(Text.From([AAAA], "it-IT"), 4), type text), #"Intervallo testo inserito" = Table.AddColumn(#"Caratteri iniziali inseriti", "Intervallo testo", each Text.Middle(Text.From([AAAA], "it-IT"), 4, 2), type text), #"Caratteri finali inseriti" = Table.AddColumn(#"Intervallo testo inserito", "Caratteri finali", each Text.End(Text.From([AAAA], "it-IT"), 2), type text), #"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Caratteri finali inseriti", "GGG", each [Caratteri finali]& "-" &[Intervallo testo]& "-" &[Caratteri iniziali]), #"Modificato tipo2" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata2",{{"GGG", type date}}), #"Rinominate colonne" = Table.RenameColumns(#"Modificato tipo2",{{"GGG", "DATA_CORRETTA"}}), #"Modificato tipo3" = Table.TransformColumnTypes(#"Rinominate colonne",{{"DATA_CORRETTA", type date}}), #"Rimosse colonne1" = Table.RemoveColumns(#"Modificato tipo3",{"Intervallo testo", "Caratteri finali", "Caratteri iniziali", "AAAA"}), #"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"
  
  If that doesn't work please provide a screenshot of your data before you try to insert my code. I need to see the names of the column(s) in your table.
  
  Regards
  
  Phil
Anonymous
Not applicable
5 years ago

Thanks for your help.
this is the query in power bi

let
Origine = Excel.Workbook(File.Contents("C:\profili\U399105\Desktop\POWER BI\V1\01_DB_GLOBALE.xlsx"), null, true),
#"01_DB_GLOBALE_Sheet" = Origine{[Item="01_DB_GLOBALE",Kind="Sheet"]}[Data],
#"Intestazioni alzate di livello" = Table.PromoteHeaders(#"01_DB_GLOBALE_Sheet", [PromoteAllScalars=true]),
#"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"ChiaveGestore", type text}, {"Gestore", Int64.Type}, {"Giorno", Int64.Type}, {"Banca", Int64.Type}, {"COD_PTF", type text}, {"TIPO_PTF", type text}, {"CodProd", type text}, {"Report", type text}, {"Prodotto", type text}, {"SaldoC", type number}, {"MeseC", type number}, {"AnnoC", type number}, {"TOT_COLLOC", type number}, {"RG_CERT", type number}, {"ClientiGestore", Int64.Type}, {"ORG_SUPERAREA_UO", Int64.Type}, {"CML", type number}, {"NumNdgC", Int64.Type}, {"MINTC", type number}, {"MINTC_upfront", type number}, {"MINTC_Mant", type number}, {"MeseRpc", Int64.Type}, {"NUM_PTF", Int64.Type}, {"COORD", type text}}),
#"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "DT_GG", each Date.FromText([Giorno],"GG MM AAAA")),
#"Modificato tipo1" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata",{{"DT_GG", type date}}),
#"Rimosse colonne" = Table.RemoveColumns(#"Modificato tipo1",{"DT_GG"}),
#"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Rimosse colonne", "AAAA", each [Giorno]),
#"Caratteri iniziali inseriti" = Table.AddColumn(#"Aggiunta colonna personalizzata1", "Caratteri iniziali", each Text.Start(Text.From([AAAA], "it-IT"), 4), type text),
#"Intervallo testo inserito" = Table.AddColumn(#"Caratteri iniziali inseriti", "Intervallo testo", each Text.Middle(Text.From([AAAA], "it-IT"), 4, 2), type text),
#"Caratteri finali inseriti" = Table.AddColumn(#"Intervallo testo inserito", "Caratteri finali", each Text.End(Text.From([AAAA], "it-IT"), 2), type text),
#"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Caratteri finali inseriti", "GGG", each [Caratteri finali]& "-" &[Intervallo testo]& "-" &[Caratteri iniziali]),
#"Modificato tipo2" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata2",{{"GGG", type date}}),
#"Rinominate colonne" = Table.RenameColumns(#"Modificato tipo2",{{"GGG", "DATA_CORRETTA"}}),
#"Modificato tipo3" = Table.TransformColumnTypes(#"Rinominate colonne",{{"DATA_CORRETTA", type date}}),
#"Rinominate colonne1" = Table.RenameColumns(#"Modificato tipo3",{{"DATA_CORRETTA", "D00_ATA_CORRETTA"}}),
#"Rimosse colonne1" = Table.RemoveColumns(#"Rinominate colonne1",{"Caratteri finali", "D00_ATA_CORRETTA", "Intervallo testo", "AAAA", "Caratteri iniziali"})
in
#"Rimosse colonne1"

the db has 24 columns

ChiaveGestore
Gestore
Giorno
Banca
COD_PTF
TIPO_PTF
CodProd
Report
Prodotto
SaldoC
MeseC
AnnoC
TOT_COLLOC
RG_CERT
ClientiGestore
ORG_SUPERAREA_UO
CML
NumNdgC
MINTC
MINTC_upfront
MINTC_Mant
MeseRpc
NUM_PTF
COORD

i'd like to convert only column GIORNO (in text format 20201231 in data format 12/31/20)
- PhilipTreacy
  Super User
  5 years ago
  Hi Anonymous
  
  If you supplied your PBIX file it would be a lot quicker. Onething that is really important is that Power Query si cas sensitive. So when you write GIORNO that is not the same as Giorno for Power Query. I need to alter my query to be the correct name for the column which is Giorno.
  
  Try this code. If it doesn't work please provide the PBIX file.
  
  let Origine = Excel.Workbook(File.Contents("C:\profili\U399105\Desktop\POWER BI\V1\01_DB_GLOBALE.xlsx"), null, true), #"01_DB_GLOBALE_Sheet" = Origine{[Item="01_DB_GLOBALE",Kind="Sheet"]}[Data], #"Intestazioni alzate di livello" = Table.PromoteHeaders(#"01_DB_GLOBALE_Sheet", [PromoteAllScalars=true]), #"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"ChiaveGestore", type text}, {"Gestore", Int64.Type}, {"Giorno", Int64.Type}, {"Banca", Int64.Type}, {"COD_PTF", type text}, {"TIPO_PTF", type text}, {"CodProd", type text}, {"Report", type text}, {"Prodotto", type text}, {"SaldoC", type number}, {"MeseC", type number}, {"AnnoC", type number}, {"TOT_COLLOC", type number}, {"RG_CERT", type number}, {"ClientiGestore", Int64.Type}, {"ORG_SUPERAREA_UO", Int64.Type}, {"CML", type number}, {"NumNdgC", Int64.Type}, {"MINTC", type number}, {"MINTC_upfront", type number}, {"MINTC_Mant", type number}, {"MeseRpc", Int64.Type}, {"NUM_PTF", Int64.Type}, {"COORD", type text}}), #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "DT_GG", each Date.FromText([Giorno],"GG MM AAAA")), #"Modificato tipo1" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata",{{"DT_GG", type date}}), #"Rimosse colonne" = Table.RemoveColumns(#"Modificato tipo1",{"DT_GG"}), #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Rimosse colonne", "AAAA", each [Giorno]), #"Caratteri iniziali inseriti" = Table.AddColumn(#"Aggiunta colonna personalizzata1", "Caratteri iniziali", each Text.Start(Text.From([AAAA], "it-IT"), 4), type text), #"Intervallo testo inserito" = Table.AddColumn(#"Caratteri iniziali inseriti", "Intervallo testo", each Text.Middle(Text.From([AAAA], "it-IT"), 4, 2), type text), #"Caratteri finali inseriti" = Table.AddColumn(#"Intervallo testo inserito", "Caratteri finali", each Text.End(Text.From([AAAA], "it-IT"), 2), type text), #"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Caratteri finali inseriti", "GGG", each [Caratteri finali]& "-" &[Intervallo testo]& "-" &[Caratteri iniziali]), #"Modificato tipo2" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata2",{{"GGG", type date}}), #"Rinominate colonne" = Table.RenameColumns(#"Modificato tipo2",{{"GGG", "DATA_CORRETTA"}}), #"Modificato tipo3" = Table.TransformColumnTypes(#"Rinominate colonne",{{"DATA_CORRETTA", type date}}), #"Rimosse colonne1" = Table.RemoveColumns(#"Modificato tipo3",{"Intervallo testo", "Caratteri finali", "Caratteri iniziali", "AAAA"}), #"Added Custom" = Table.AddColumn(#"Rimosse colonne1", "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"
  
  Regards
PhilipTreacy
Super User
5 years ago
Hi Anonymous

How exactly doesn't it work? Can you be specific about the error?

Thanks for the PBIX file but without your source Excel data I can't do anything. I can open your query but I can't really work on it as I can't see the columsn or what your transformations steps are doing.

I can see that it looks like you've tried multiple ways to convert the column but I don't knwo what state the table is in at the end of all of these steps.

Can you please provide the file C:\profili\U399105\Desktop\POWER BI\V1\01_DB_GLOBALE.xlsx

If it has confidential info you can Prvate Message it to me.

regards

Phil
- Anonymous
  Not applicable
  5 years ago
  PhilipTreacy i send you a pm with excel
  thanks for the infinite patience and the invaluable help
- Anonymous
  Not applicable
  5 years ago
  PhilipTreacy I send you a file
  thanks

Forum Discussion

Help convert text to data and convent number to data

12 Replies

Related content

Pipeline Naming Convention

Convert text to number

Convert Number to Date

Convert Text to number with DAX

Convert numbers to month