Filling up and down from a specific column

[MoussaBAK]

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 the lines B2 to B4 concern all the individuals whereas and he information contained in the line B5 at the end of the file are specific to each individual.

I operated the retreatment whose code is the following one:

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"
),
#"Rempli vers le bas" = Table.FillDown(
#"Colonne dynamique",
{"Société ", "Mois concerné ", "Adresse "}
),
#"Rempli vers le haut" = Table.FillUp(
#"Rempli vers le bas",
{"Nom ", "Prénom", "Date de naissance ", "Poste ", "Nom de jeune fille "}
),
#"Lignes filtrées" = Table.SelectRows(#"Rempli vers le haut", each ([#"Matricule "] <> null))
in
#"Lignes filtrées"

- I would like to establish a formula allowing to fill down all the columns to the left of the column [#"Personnel number "] and up all the columns to the right of the column [#"Personnel number "]. My goal is to avoid bugs when adding or removing columns.
- the "Maiden name" field appears for an individual only (TRANKIL). This generates a problem in my reprocessing since all the individuals have a "Maiden name" as shown in the figure below: 

I really don't see how to solve this problem. Can you please help me?

my example file : 

https://onedrive.live.com/view.aspx?resid=76CCAFF1BF95E405%2133270&id=documents

Thank you 

[v-jingzhang · Accepted solution]

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.

[MoussaBAK]

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]

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]

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 

[Vijay_A_Verma]

One drive link gives error - 

This item might not exist or is no longer available

[MoussaBAK]

https://we.tl/t-qcoubwocwO 

[MoussaBAK]

https://we.tl/t-qcoubwocwO 

[MoussaBAK]

Hi Vijay, 

Thanks for you answer this is a new link.

https://1drv.ms/x/s!AgXklb_xr8x2goN22uEtyDK5im81BA?e=Lt3Wca