Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
MoussaBAK
Frequent Visitor

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:

MoussaBAK_0-1662110275036.png

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: 
 
MoussaBAK_1-1662110595474.png

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 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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.

View solution in original post

8 REPLIES 8
v-jingzhang
Community Support
Community Support

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.

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 

4Société AAvril 33 rue Power BI001BILLAMouloud 10/12/1999Cadre CALME
9Société AAvril 33 rue Power BI002BOULLMoussa10/08/1999Cadre CALME
14Société AAvril 33 rue Power BI003MILLARoger10/07/1970Non cadre CALME
19Société AAvril 33 rue Power BI003TRANKILSarah10/04/78Non cadre CALME

 

The expected result is :

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

4Société AAvril 33 rue Power BI001BILLAMouloud 10/12/1999Cadre  
9Société AAvril 33 rue Power BI002BOULLMoussa10/08/1999Cadre  
14Société AAvril 33 rue Power BI003MILLARoger10/07/1970Non cadre  
19Société AAvril 33 rue Power BI003TRANKILSarah10/04/78Non cadre CALME

 

Thanks and regards 

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

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
Super User
Super User

One drive link gives error - 

This item might not exist or is no longer available

Hi Vijay, 

 

Thanks for you answer this is a new link.

 

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

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.