Transforming rows into columns

[VJ_Paris]

Hello

I would like to transform a table with rows into columns

As you can see I need to get the field with the date on each rows.

I don't need the column Type

Some time there is a Nom d'usage and sometime no. 

The number of rows per month is different for each month.

Could someone help me please to transform that with power query ?

Many thanks

Original Table :

oct-22
Type	Info
Nom de Famille	A
Nom d'usage	A
Prénom	P1
Matricule	004101382
Numéro contrat	004043992
nov-22
Type	Info
Nom de Famille	TOTO
Prénom	P2
Matricule	082102197
Numéro contrat	082826726
Nom de Famille	TATIE
Prénom	P3
Matricule	082102231
Numéro contrat	082826809
Nom de Famille	DADA
Prénom	P4
Matricule	060001113
Numéro contrat	060602106

Expected table : 

Nom de Famille	Nom d'usage	Prénom	Matricule	Numéro contrat	Date
A	A	P1	004101382	004043992	oct-22
TOTO		P2	082102197	082826726	nov-22
TATIE		P3	082102231	082826809	nov-22
DADA		P4	060001113	060602106	nov-22

[VJ_Paris]

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" 

[VJ_Paris]

Hi, Many thanks for your answer It looks fine ! I will try it

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldBLCoMwEAbgq0jWFmYmEuNSsAUXrS7cWRdibRHUFKuFHslzeLH6WAg+Ct1l5if5ZhKGTCX1gYjpjEV6yILPM+3PbnlXY31RhXZLtVNcZHk+JPbcvjYAZDav+DEHftW1pSr62sexcY7rKkua8a6BgFzS9EJTdG2ltESVdRXXQwoGt6wpLdX7n6ECL/CWPK14SQiElrnjS5IkTBI7hB24x6XBtw3i+MuQYG0bju2sftFYEQIAEJHvEAJEvyX0a0Rf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "Type")),

    #"Grouped Table" = Table.Group(#"Filtered Rows", "Column1", {"Grp Mois", each Table.Group(Table.Skip(_), "Column1", {"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)),
    #"Expanded Grp Mois" = Table.ExpandTableColumn(#"Grouped Table", "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"