Forum Discussion

ecureuildunord's avatar
ecureuildunord
New Member
2 years ago

Split a time returns NULL for seconds

Hello community,   I have this column that has and "hour" format in PowerQuery hh:mm:ss (ex: 13:54:46) When I split using the : separator, Power Query split in 3 columns, but the last column has n...
Need Help
AlienSx's avatar
AlienSx
Super User
2 years ago

Hello, ecureuildunord PQ operates with types, not formats. Types are text, date, time, datetime, number etc. You are probably dealing with type time. You can't split time as text (using delimiter). You have to deal with PQ time functions, Time.ToRecord in this case. Say you have some table "your_table" and your column is called "hour". Then create blank query, open Advanced Editor and copy - paste this code in there 

let
    Source = your_table,
    txform = Table.TransformColumns(Source, {"hour", Time.ToRecord}),
    expand = Table.ExpandRecordColumn(txform, "hour", {"Hour", "Minute", "Second"})
in
    expand

I am sorry I don't know how to do that using just PQ UI. 

ecureuildunord2's avatar
ecureuildunord2
New Member
2 years ago

Thanks for trying to help out! Much appreciated.

 

I have tried to paste your code at the end of the previously generated code by the PQ UI. No luck. I have never programmed in such a language. 😐

 

let
    Source = Excel.Workbook(Web.Contents("https://msss365-my.sharepoint.com/personal/claude_ferron_ssss_gouv_qc_ca/Documents/01-Analyse%20Registre%20Virtuo/Data/99-Interrogation_Registre.xlsx"), null, true),
    #"99-Interrogation_Registre_Sheet" = Source{[Item="99-Interrogation_Registre",Kind="Sheet"]}[Data],
    #"En-têtes promus1" = Table.PromoteHeaders(#"99-Interrogation_Registre_Sheet", [PromoteAllScalars=true]),
    #"Premières lignes supprimées" = Table.Skip(#"En-têtes promus1",1),
    #"Type modifié3" = Table.TransformColumnTypes(#"Premières lignes supprimées",{{"Code", type text}, {"Date & hre", type any}, {"Description", type text}, {"Type", Int64.Type}, {"Nom de l'utilisateur", type text}, {"Nom logiciel", type text}}),
    #"Fractionner la colonne par délimiteur" = Table.SplitColumn(Table.TransformColumnTypes(#"Type modifié3", {{"Date & hre", type text}}, "fr-CA"), "Date & hre", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Date & hre.1", "Date & hre.2"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Fractionner la colonne par délimiteur",{{"Date & hre.1", type date}, {"Date & hre.2", type time}}),
    #"Fractionner la colonne par délimiteur1" = Table.SplitColumn(#"Type modifié", "Description", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Description.1", "Description.2"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Fractionner la colonne par délimiteur1",{{"Description.1", type text}, {"Description.2", type text}}),
    #"Colonnes renommées" = Table.RenameColumns(#"Type modifié1",{{"Description.1", "Module"}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Colonnes renommées",{"Type"}),
    #"Fractionner la colonne par délimiteur2" = Table.SplitColumn(#"Colonnes supprimées", "Description.2", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Description.2.1", "Description.2.2"}),
    #"Type modifié2" = Table.TransformColumnTypes(#"Fractionner la colonne par délimiteur2",{{"Description.2.1", type text}, {"Description.2.2", type text}}),
    #"Diviser la colonne selon les transitions de caractères" = Table.SplitColumn(#"Type modifié2", "Description.2.1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Description.2.1.1", "Description.2.1.2"}),
    #"Valeur remplacée" = Table.ReplaceValue(#"Diviser la colonne selon les transitions de caractères",".","",Replacer.ReplaceText,{"Description.2.1.1"}),
    #"Valeur remplacée1" = Table.ReplaceValue(#"Valeur remplacée",".","",Replacer.ReplaceText,{"Description.2.2"}),
    #"Colonnes permutées" = Table.ReorderColumns(#"Valeur remplacée1",{"Code", "Nom de l'utilisateur", "Date & hre.1", "Date & hre.2", "Nom logiciel", "Module", "Description.2.1.1", "Description.2.1.2", "Description.2.2"}),
    #"Colonnes renommées1" = Table.RenameColumns(#"Colonnes permutées",{{"Description.2.1.1", "Type de transaction"}, {"Description.2.1.2", "Employé modifié"}, {"Description.2.2", "Modification"}, {"Date & hre.1", "Date"}, {"Date & hre.2", "Heure"}}),
    #"Duplication de la colonne" = Table.DuplicateColumn(#"Colonnes renommées1", "Heure", "Heure - Copier"),
    #"Colonnes permutées1" = Table.ReorderColumns(#"Duplication de la colonne",{"Code", "Nom de l'utilisateur", "Date", "Heure", "Heure - Copier", "Nom logiciel", "Module", "Type de transaction", "Employé modifié", "Modification"}),
    #"Fractionner la colonne par délimiteur3" = Table.SplitColumn(Table.TransformColumnTypes(#"Colonnes permutées1", {{"Heure - Copier", type text}}, "fr-CA"), "Heure - Copier", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Heure - Copier.1", "Heure - Copier.2"}),
    #"Type modifié4" = Table.TransformColumnTypes(#"Fractionner la colonne par délimiteur3",{{"Heure - Copier.1", Int64.Type}, {"Heure - Copier.2", Int64.Type}, {"Employé modifié", type text}}),
    #"Colonnes renommées2" = Table.RenameColumns(#"Type modifié4",{{"Heure - Copier.1", "Hr"}, {"Heure - Copier.2", "Mn"}}),
    #"Duplication de la colonne1" = Table.DuplicateColumn(#"Colonnes renommées2", "Heure", "Heure - Copier")
in
    #"Duplication de la colonne1"

let
    Excel.Workbook(Web.Contents("https://msss365-my.sharepoint.com/personal/claude_ferron_ssss_gouv_qc_ca/Documents/01-Analyse%20Registre%20Virtuo/Data/99-Interrogation_Registre.xlsx"), null, true),
    txform = Table.TransformColumns(Source, {"Heure - Copier", Time.ToRecord}),
    expand = Table.ExpandRecordColumn(txform, "Heure - Copier", {"Hour", "Minute", "Second"})