let
Source = Binary.Decompress(File.Contents("C:\Users\cousi\Documents\Excel\exemples\PQ\csv\essai\full.csv.gz"), Compression.GZip),
#"CSV importé" = Csv.Document(Source,[Delimiter=",", Columns=40, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"En-têtes promus" = Table.PromoteHeaders(#"CSV importé", [PromoteAllScalars=true]),
#"Lignes filtrées1" = Table.SelectRows(#"En-têtes promus", each ([code_departement] = "974")),
#"Type modifié" = Table.TransformColumnTypes(#"Lignes filtrées1",{{"id_mutation", type text}, {"date_mutation", type date}, {"numero_disposition", Int64.Type}, {"nature_mutation", type text}, {"valeur_fonciere", type number}, {"adresse_numero", Int64.Type}, {"adresse_suffixe", type text}, {"adresse_nom_voie", type text}, {"adresse_code_voie", type text}, {"code_postal", Int64.Type}, {"code_commune", Int64.Type}, {"nom_commune", type text}, {"code_departement", Int64.Type}, {"ancien_code_commune", type text}, {"ancien_nom_commune", type text}, {"id_parcelle", type text}, {"ancien_id_parcelle", type text}, {"numero_volume", type text}, {"lot1_numero", Int64.Type}, {"lot1_surface_carrez", type number}, {"lot2_numero", Int64.Type}, {"lot2_surface_carrez", type number}, {"lot3_numero", Int64.Type}, {"lot3_surface_carrez", type number}, {"lot4_numero", type text}, {"lot4_surface_carrez", type text}, {"lot5_numero", type text}, {"lot5_surface_carrez", type text}, {"nombre_lots", Int64.Type}, {"code_type_local", Int64.Type}, {"type_local", type text}, {"surface_reelle_bati", Int64.Type}, {"nombre_pieces_principales", Int64.Type}, {"code_nature_culture", type text}, {"nature_culture", type text}, {"code_nature_culture_speciale", type text}, {"nature_culture_speciale", type text}, {"surface_terrain", Int64.Type}, {"longitude", type number}, {"latitude", type number}}),
#"Autres colonnes supprimées" = Table.SelectColumns(#"Type modifié",{"id_mutation", "valeur_fonciere", "code_departement", "code_type_local", "surface_terrain"}),
#"Lignes filtrées" = Table.SelectRows(#"Autres colonnes supprimées", each ([valeur_fonciere] <> null) and ([surface_terrain] <> null)),
#"Valeur remplacée" = Table.ReplaceValue(#"Lignes filtrées",null,"None",Replacer.ReplaceValue,{"code_type_local", "valeur_fonciere", "surface_terrain"}),
#"Type modifié1" = Table.TransformColumnTypes(#"Valeur remplacée",{{"valeur_fonciere", type number}, {"surface_terrain", type number}, {"code_type_local", type text}}),
#"Lignes groupées" = Table.Group(#"Type modifié1", {"id_mutation"}, {{"code", each Text.Combine(List.Distinct([code_type_local]),", ")}, {"Toutes", each _, type table [id_mutation=nullable text, valeur_fonciere=nullable number, code_departement=nullable number, code_type_local=nullable text, surface_terrain=nullable number]}}),
#"Lignes filtrées2" = Table.SelectRows(#"Lignes groupées", each ([code] = "None")),
#"Toutes développé" = Table.ExpandTableColumn(#"Lignes filtrées2", "Toutes", {"valeur_fonciere", "code_departement", "code_type_local", "surface_terrain"}, {"Toutes.valeur_fonciere", "Toutes.code_departement", "Toutes.code_type_local", "Toutes.surface_terrain"})
in
#"Toutes développé"