Forum Discussion
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 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:
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"
- 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
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.
8 Replies
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
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
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
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
