Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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:
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
Solved! Go to 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.
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
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
Hi Vijay,
Thanks for you answer this is a new link.
https://1drv.ms/x/s!AgXklb_xr8x2goN22uEtyDK5im81BA?e=Lt3Wca
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
27 | |
12 | |
12 | |
11 | |
9 |
User | Count |
---|---|
53 | |
29 | |
17 | |
14 | |
13 |