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 null value in it.

Any idea what is going on?

 

My PowerQuery level is complete newbie by the way.

 

Thanks for your help.

 

CF

Need Help

2 Replies

  • 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"})