Add custom field to a "pivot table" - Power Query

Question

I've used Power Query to add custom fields to a table made from 2 merged tables in order to simulate a pivot table. However, I can't seem to add a filter to my final table. Is there another way to do this?

I've tried to use the Pivot table from Excel, but I can't seem to insert calculated field as desired.

Here's my Excel file: https://ufile.io/x2v1j

Excel's pivot table is not what you want, that's right. From your description it is not clear what do you want to do with your table. Adding a filter to resulting table is possible both in Excel and in PowerQuery. Adding a function as a filter is only possible with PowerQuery. — Eugene

Marc Pincince Marc Pincince · Accepted Answer · 2018-10-30T21:07:48

I'll start with a disclaimer that I'm not exactly sure I know what you're trying to do; but I took a stab at this anyway.

I figured you were trying to filter the months in the T_Catégories query, before your grouping; so I added a manual filter step there. When I did that and deselected months, your T_Final query broke. The reason is because, as I filtered out months, it also filtered out categories that your T_Final query relied upon for column names. For instance, this affected your calculations that relied upon column names. I had to change your T_Final query so that it would dynamically determine the column names.

Again, I'm not exactly sure about what you're trying to do, so I may have gotten it wrong with respect to the calculations, but this might help get you closer at least.

Like I said, in T_Catégories, I added the filter:

That's when things broke for T_Final. So in T_Final, I needed to:

Change the step Valeur remplacée1 to = Table.ReplaceValue(#"Colonne dynamique",null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Colonne dynamique")) (I was pretty sure you were using the columns resulting from the previous step Colonne dynamique.)

Change the step Personnalisée ajoutée3 to = Table.AddColumn(#"Valeur remplacée1", "Total général", each List.Sum(List.RemoveFirstN(Record.ToList(_),1))) (This is making a list from the record, then removing the first entry of the list and summing what remains in the list.)

Change the step Colonnes permutées to = Table.ReorderColumns(#"Personnalisée ajoutée3",Table.ColumnNames(#"Personnalisée ajoutée3")) (I was pretty sure you were using the column resulting from the previous step Personnalisée ajoutée3.)

Change the step Personnalisée ajoutée to = Table.AddColumn(#"Colonnes permutées", "Indisponibilté", each List.Sum(List.RemoveLastN(List.RemoveFirstN(Record.ToList(_),1),2))) (This is making a list from the record, then removing the first entry of the list, then removing the last two entries of the list, and summing what remains in the list. This is especially where I'm not sure I added the items you intended. At least you can see what I did to be able to add the columns without using static column names.)

Here's the m code for the three queries:

T_Catégories:

let
Source = Excel.CurrentWorkbook(){[Name="T_Catégories"]}[Content],
#"Type modifié" = Table.TransformColumnTypes(Source,{{"Métier", type text}, {"Code absence", Int64.Type}, {"Date", type date}, {"Catégorie", type text}}),
#"Colonnes supprimées" = Table.RemoveColumns(#"Type modifié",{"Code absence", "Date"}),
#"Filtered Rows" = Table.SelectRows(#"Colonnes supprimées", each true),
#"Lignes groupées" = Table.Group(#"Filtered Rows", {"Métier", "Catégorie"}, {{"Nombre", each Table.RowCount(_), type number}})
in
#"Lignes groupées"

T_métiers:

let
Source = Excel.CurrentWorkbook(){[Name="T_métiers"]}[Content],
#"Type modifié" = Table.TransformColumnTypes(Source,{{"Métier", type text}, {"Nombre", Int64.Type}})
in
#"Type modifié"

T_Final:

let
Source = Table.Combine({T_Catégories, T_métiers}),
#"Valeur remplacée" = Table.ReplaceValue(Source,null,"Nombre employés",Replacer.ReplaceValue,{"Catégorie"}),
#"Colonne dynamique" = Table.Pivot(#"Valeur remplacée", List.Distinct(#"Valeur remplacée"[Catégorie]), "Catégorie", "Nombre"),
#"Valeur remplacée1" = Table.ReplaceValue(#"Colonne dynamique",null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Colonne dynamique")),
#"Personnalisée ajoutée3" = Table.AddColumn(#"Valeur remplacée1", "Total général", each List.Sum(List.RemoveFirstN(Record.ToList(_),1))),
#"Colonnes permutées" = Table.ReorderColumns(#"Personnalisée ajoutée3",Table.ColumnNames(#"Personnalisée ajoutée3")),
#"Personnalisée ajoutée" = Table.AddColumn(#"Colonnes permutées", "Indisponibilté", each List.Sum(List.RemoveLastN(List.RemoveFirstN(Record.ToList(_),1),2))),
#"Personnalisée ajoutée1" = Table.AddColumn(#"Personnalisée ajoutée", "Disponibilté", each [Nombre employés]*7.5),
#"Personnalisée ajoutée2" = Table.AddColumn(#"Personnalisée ajoutée1", "Taux disponibilté (%)", each (1-[Indisponibilté]/[Disponibilté])*100),
#"Type modifié" = Table.TransformColumnTypes(#"Personnalisée ajoutée2",{{"Indisponibilté", Int64.Type}, {"Disponibilté", type number}, {"Taux disponibilté (%)", type number}})
in
#"Type modifié"

I would think you can progress from here fairly well.

Add custom field to a "pivot table" - Power Query

1 Answers