Forum Discussion
Filling up and down from a specific column
Hi MoussaBAK
Here is my solution. The excel file is attached at bottom.
let Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content], #"Type modifié" = Table.TransformColumnTypes(Source,{{"Colonne1", type text}, {"Colonne2", type text}}), #"Colonnes renommées" = Table.RenameColumns(#"Type modifié",{{"Colonne1", "Rubrique"}, {"Colonne2", "Informations"}}), #"Index ajouté" = Table.AddIndexColumn(#"Colonnes renommées", "Index", 1, 1, Int64.Type), #"Colonne dynamique" = Table.Pivot(#"Index ajouté", List.Distinct(#"Index ajouté"[Rubrique]), "Rubrique", "Informations"), // get the list of table column names column_names = Table.ColumnNames(#"Colonne dynamique"), // get the position index of "Matricule " in the name list position_of_Matricule = List.PositionOf(column_names, "Matricule "), #"Rempli vers le bas" = Table.FillDown(#"Colonne dynamique",List.Range(column_names, 0, position_of_Matricule)), #"Rempli vers le haut" = Table.FillUp(#"Rempli vers le bas",List.Range(column_names, position_of_Matricule + 1, List.Count(column_names) - 1 - position_of_Matricule)), #"Lignes filtrées" = Table.SelectRows(#"Rempli vers le haut", each ([#"Matricule "] <> null)) in #"Lignes filtrées"Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Community SupportHi MoussaBAK
Besides the "Nom de jeune fille" field, will other fields be possible to have the same problem that they appear for only one individual? I'm trying to find a solution to deal with any possible field that may have this problem, but I haven't succeeded yet.
Best Regards,
Community Support Team _ Jing
Besides the "Maiden Name" field, will other fields be able to have the same problem of only appearing for one individual? I am trying to find a solution to deal with all the fields that may have this problem, but I have not yet succeeded.
Hello,
Indeed other fields are succeptible to have a field that appears only for one individual or more values for the same field and for the same individual.
I thought of solving the problem in another way:
- to isolate the lines for the individuals only
- Rotate the columns so that the field and the information are placed one below the other.
- Split" the column according to the matricule occurrence, but again I'm stuck because the List.Split function only accepts numbers and I don't see what other function to use. I tried Table.FromList but I don't think I know enough about coding.
I created a tab "solution2" in the attached file to illustrate the following code:
let
Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
#"Type modifié" = Table.TransformColumnTypes(Source,{{"Colonne1", type text}, {"Colonne2", type text}}),
#"Index ajouté" = Table.AddIndexColumn(#"Type modifié", "Index", 0, 1, Int64.Type),
#"Colonnes supprimées" = Table.RemoveColumns(#"Index ajouté",{"Index"}),
#"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Colonnes supprimées", "Index", each if [Colonne1] = "Société " then "Général" else if [Colonne1] = "Matricule " then [Colonne2] else null),
#"Rempli vers le bas" = Table.FillDown(#"Colonne conditionnelle ajoutée",{"Index"}),
#"Lignes filtrées" = Table.SelectRows(#"Rempli vers le bas", each ([Index] <> "Général")),
#"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Lignes filtrées", {"Index"}, "Attribut", "Valeur"),
#"Colonnes supprimées1" = Table.RemoveColumns(#"Supprimer le tableau croisé dynamique des autres colonnes",{"Attribut", "Index"}),
Valeur = #"Colonnes supprimées1"[Valeur]
in
Valeur