Filling up and down from a specific column

Hello to all, I'm new to PowerQuery and I'm trying to reprocess a database file. This file has the particularity to have the data stacked on 2 columns as follows: The information contained in...

Need Help

v-jingzhang

3 years ago

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.

Exemple de retraitement simple.xlsx21 KB

MoussaBAK

Frequent Visitor

3 years ago

Hi, v-jingzhang

Thank you very much for your answer and for the associated comments in the code. It allows me to move forward with a fuid understanding.

There remains, however, the second problem that I take up: the "Nom de jeune fille" field appears for an individual only (TRANKIL). This generates a problem in my reprocessing since all the individuals have a "Nom de jeune fille" as shown in the figure below:

IndexSociété Mois concerné Adresse Matricule Nom PrénomDate de naissance Poste Nom de jeune fille

4	Société A	Avril	33 rue Power BI	001	BILLA	Mouloud	10/12/1999	Cadre	CALME
9	Société A	Avril	33 rue Power BI	002	BOULL	Moussa	10/08/1999	Cadre	CALME
14	Société A	Avril	33 rue Power BI	003	MILLA	Roger	10/07/1970	Non cadre	CALME
19	Société A	Avril	33 rue Power BI	003	TRANKIL	Sarah	10/04/78	Non cadre	CALME

The expected result is :

IndexSociété Mois concerné Adresse Matricule Nom PrénomDate de naissance Poste Nom de jeune fille

4	Société A	Avril	33 rue Power BI	001	BILLA	Mouloud	10/12/1999	Cadre
9	Société A	Avril	33 rue Power BI	002	BOULL	Moussa	10/08/1999	Cadre
14	Société A	Avril	33 rue Power BI	003	MILLA	Roger	10/07/1970	Non cadre
19	Société A	Avril	33 rue Power BI	003	TRANKIL	Sarah	10/04/78	Non cadre	CALME

Thanks and regards

v-jingzhang
Community Support
3 years ago
Hi 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
- MoussaBAK
  Frequent Visitor
  3 years ago
  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
  
  https://we.tl/t-mHp6Y01pKI

Forum Discussion

Filling up and down from a specific column

Related content

Filled map

Wrap text in specific columns?

Updating specific columns

Specific matrix

Filled map