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

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

Forum Discussion

Help convert text to data and convent number to data

Related content

Pipeline Naming Convention

Convert text to number

Convert Number to Date

Microsoft Fabric naming conventions

Convert Text to number with DAX