Forum Discussion
Transforming rows into columns
Hello, I tried the script provided, it works perfectly well with the source given as example. But when I try to change the source by a another like a tab, there are some error message about the format of the date ?
Could you help me please to adapt the script.
Message error : Expression.Error : Désolé... Nous n'avons pas pu convertir la valeur #datetime(2023, 2, 1, 0, 0, 0) en type Text.
Détails : Value=01/02/2023 00:00:00
Type=[Type]
As the language is in French, I replaced Column by Colonne
-------------------------------------------------------------------------
let
Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Colonne1] <> "Type")),
#"Grouped Table" = Table.Group(#"Filtered Rows", "Colonne1", {"Grp Mois", each Table.Group(Table.Skip(_), "Colonne1", {"Grp Nom", each _}, 0, (x,y) => Byte.From(y="Nom de Famille"))}, 0, (x,y) => Byte.From(Text.PositionOfAny(y, {"0".."9"}, Occurrence.First) <> -1)),
#"Grp Mois développé" = Table.ExpandTableColumn(#"Grouped Table", "Grp Mois", {"Colonne1", "Grp Nom"}, {"Grp Mois.Colonne1", "Grp Mois.Grp Nom"}),
#"Expanded Grp Mois" = Table.ExpandTableColumn(#"Grp Mois développé", "Grp Mois", {"Grp Nom"}), #"Transformed Column" = Table.TransformColumns(#"Expanded Grp Mois", {"Grp Nom", each Table.PromoteHeaders(Table.Transpose(_))}),
#"Expanded Grp Nom" = Table.ExpandTableColumn(#"Transformed Column", "Grp Nom", {"Nom de Famille", "Nom d'usage", "Prénom", "Matricule", "Numéro contrat"})
in
#"Expanded Grp Nom"