Create a column which sum an amount according other column

[Anonymous]

Hi everybody,

I have currently the following first 4 columns and I want to create the fifth one (Breakdowns count) which will sum the amount of "Order" (column 4) per "Equipment" and per "WEEK" and per "MONTH".

Example, for the month 1, week 1, equipment No 1000406533 : we had 3 different orders (an order is created when a breakdown occurs)  

MONTH	WEEK	Equipment	Order	Breakdowns count
1	1	1000406531	910308940	1
1	1	1000406533	910308045	3
1	1	1000406533	910308045
1	1	1000406533	910308270
1	1	1000406533	910308299
1	1	1000406533	910308299
1	1	1000406535	910308586	1
1	1	1000406535	910308586
1	1	1000406536	910308297	1
1	1	1000406538	910308121	2
1	1	1000406538	910308988
1	1	1000406538	910308988

What would be the M code to create that 5th column ? I hope I was clear enough, I precise that I am a rookie in using Power Query, thank you very much for your help !

[Anonymous]

That is a great solution too ! 

But I have an issue. The query helps me to sort data which goes right after that into a pivot table. 

In my pivot table I added all fields I need and everything seems correct.

And the last step is that I call data from the pivot table to have it in a classic table. When I am calling the "Breakdowns count" from pivot table, instead of giving me the getpivotdata formula it gaves me the cell :

Any idea why it doesn't work ? Here the file : Download | file.io

[dufoq3]

Hi @Anonymous,

another approach:

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rc9BCsAgDATAr4hnKZtoNHlL8f/fqEWQHtoqtIfAhgyE3XdPPvQBkJAlknOu7UaIUEvoK23ia7jhcVgkaZl/YlzQMuZv2azl/InJYKL5re0yzJfH5bmvDkbcjxNmeua4zOoB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"MONTH " = _t, #"WEEK   " = _t, Equipment = _t, Order = _t, #"Breakdown duration" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"MONTH ", "WEEK   ", "Equipment"}, {
    {"Breakdowns count", each Table.RowCount(Table.Distinct(_)), Int64.Type},
    {"Detail", each Table.AddColumn(Table.AddIndexColumn(_, "Index", 0, 1), "Breakdown duration correct", (x)=> try if x[Order] = [Order]{x[Index]-1} then null else x[Breakdown duration] otherwise x[Breakdown duration]), type table}
}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Detail", "Breakdowns count"}),
    #"Expanded Detail" = Table.ExpandTableColumn(#"Removed Other Columns", "Detail", Table.ColumnNames(Source) & {"Breakdown duration correct"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Detail",{"Breakdown duration"})
in
    #"Removed Columns"

[Anonymous]

This works perfectly thanks 

[Anonymous]

Thanks but the result is so weird in my side. I don't know why all columns deleted in the previous steps come back when I integrate your M code.

Would you please integrate your solution in this code :

let
    Source = Excel.CurrentWorkbook(){[Name="TableIW47"]}[Content],
    #"Erreurs supprimées" = Table.RemoveRowsWithErrors(Source, {"WEEK"}),
    #"Valeur remplacée" = Table.ReplaceValue(#"Erreurs supprimées","",null,Replacer.ReplaceValue,{"Equipment"}),
    #"Rempli vers le bas" = Table.FillDown(#"Valeur remplacée",{"Equipment"}),
    #"Lignes filtrées" = Table.SelectRows(#"Rempli vers le bas", each ([Breakdown and Hot Call] = "X")),
    #"Autres colonnes supprimées" = Table.SelectColumns(#"Lignes filtrées",{"Order", "Actual work", "Equipment", "Breakdown and Hot Call", "Breakdown Duration", "WEEK", "MONTH"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Autres colonnes supprimées",{"MONTH", "WEEK", "Equipment", "Order", "Actual work", "Breakdown and Hot Call", "Breakdown Duration"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"MONTH", Order.Ascending}, {"WEEK", Order.Ascending}, {"Equipment", Order.Ascending}, {"Order", Order.Ascending}})
in
    #"Sorted Rows"

Thank you very much

[Vijay_A_Verma]

Use this

let
    Source = Excel.CurrentWorkbook(){[Name="TableIW47"]}[Content],
    #"Erreurs supprimées" = Table.RemoveRowsWithErrors(Source, {"WEEK"}),
    #"Valeur remplacée" = Table.ReplaceValue(#"Erreurs supprimées","",null,Replacer.ReplaceValue,{"Equipment"}),
    #"Rempli vers le bas" = Table.FillDown(#"Valeur remplacée",{"Equipment"}),
    #"Lignes filtrées" = Table.SelectRows(#"Rempli vers le bas", each ([Breakdown and Hot Call] = "X")),
    #"Autres colonnes supprimées" = Table.SelectColumns(#"Lignes filtrées",{"Order", "Actual work", "Equipment", "Breakdown and Hot Call", "Breakdown Duration", "WEEK", "MONTH"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Autres colonnes supprimées",{"MONTH", "WEEK", "Equipment", "Order", "Actual work", "Breakdown and Hot Call", "Breakdown Duration"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"MONTH", Order.Ascending}, {"WEEK", Order.Ascending}, {"Equipment", Order.Ascending}, {"Order", Order.Ascending}}),
   #"Grouped Rows" = Table.Combine(Table.Group(#"Sorted Rows", {"MONTH", "WEEK", "Equipment"}, {{"All", each Table.FromColumns(Table.ToColumns(_) & {{List.Count(List.Distinct(_[Order]))} & List.Repeat({null}, Table.RowCount(_)-1)}, Table.ColumnNames(_) & {"Breakdowns count"})}})[All])
in
    #"Grouped Rows"

[Anonymous]

Thanks it works but I would need help to make another operation : Let me add a 6th column that I need it modified. As you can see, in this 6th column, we have the breakdown durations which are repeated as Orders are repeated. 

MONTH	WEEK	Equipment	Order	Breakdowns count	Breakdown duration
1	1	1000406531	910308940	1	1.5
1	1	1000406533	910308045	3	2
1	1	1000406533	910308045		2
1	1	1000406533	910308270		0.5
1	1	1000406533	910308299		6
1	1	1000406533	910308299		6
1	1	1000406535	910308586	1	1.5
1	1	1000406535	910308586		1.5
1	1	1000406536	910308297	1	2
1	1	1000406538	910308121	2	1
1	1	1000406538	910308988		3
1	1	1000406538	910308988		3

So I need the 6th column to not repeat and to provide me only one breakdown duration per Order please :

MONTH	WEEK	Equipment	Order	Breakdowns count	Breakdown duration
1	1	1000406531	910308940	1	1.5
1	1	1000406533	910308045	3	2
1	1	1000406533	910308045		null
1	1	1000406533	910308270		0.5
1	1	1000406533	910308299		6
1	1	1000406533	910308299		null
1	1	1000406535	910308586	1	1.5
1	1	1000406535	910308586		null
1	1	1000406536	910308297	1	2
1	1	1000406538	910308121	2	1
1	1	1000406538	910308988		3

Here is my current code :

let
    Source = Excel.CurrentWorkbook(){[Name="Choix"]}[Content],
    #"Rempli vers le bas" = Table.FillDown(Source,{"Column1"}),
    #"En-têtes promus" = Table.PromoteHeaders(#"Rempli vers le bas", [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"LINES", type text}, {"MACHINE", type text}, {"Equipement No", Int64.Type}}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Type modifié", {"Equipement No"}, Data, {"Equipment"}, "Data", JoinKind.Inner),
    #"Data développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Data", {"Order", "Actual work", "Breakdown and Hot Call", "Breakdown Duration", "WEEK", "MONTH"}, {"Order", "Actual work", "Breakdown and Hot Call", "Breakdown Duration", "WEEK", "MONTH"}),
    #"Added Conditional Column" = Table.AddColumn(#"Data développé", "Nb of hours/week", each if [Equipement No] >= 1000409774 then 168 else if [Equipement No] >= 1000409754 then "168" else if [Equipement No] >= 1000407443 then 112 else if [Equipement No] >= 1000407440 then 56 else 112, type any),
    #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"WEEK", Order.Ascending}, {"LINES", Order.Ascending}, {"MACHINE", Order.Ascending}, {"Order", Order.Ascending}}),
    #"Grouped Rows" = Table.Combine(Table.Group(#"Sorted Rows", {"MONTH", "WEEK", "Equipement No"}, {{"All", each Table.FromColumns(Table.ToColumns(_) & {{List.Count(List.Distinct(_[Order]))} & List.Repeat({null}, Table.RowCount(_)-1)}, Table.ColumnNames(_) & {"Breakdowns count"})}})[All]),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"MONTH", "WEEK", "LINES", "MACHINE", "Equipement No", "Order", "Breakdowns count", "Actual work", "Breakdown and Hot Call", "Breakdown Duration", "Nb of hours/week"})
in
    #"Reordered Columns"

Thanks a lot for the help !!

[Vijay_A_Verma · Accepted solution]

Use this

let
    Source = Excel.CurrentWorkbook(){[Name="Choix"]}[Content],
    #"Rempli vers le bas" = Table.FillDown(Source,{"Column1"}),
    #"En-têtes promus" = Table.PromoteHeaders(#"Rempli vers le bas", [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"LINES", type text}, {"MACHINE", type text}, {"Equipement No", Int64.Type}}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Type modifié", {"Equipement No"}, Data, {"Equipment"}, "Data", JoinKind.Inner),
    #"Data développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "Data", {"Order", "Actual work", "Breakdown and Hot Call", "Breakdown Duration", "WEEK", "MONTH"}, {"Order", "Actual work", "Breakdown and Hot Call", "Breakdown Duration", "WEEK", "MONTH"}),
    #"Added Conditional Column" = Table.AddColumn(#"Data développé", "Nb of hours/week", each if [Equipement No] >= 1000409774 then 168 else if [Equipement No] >= 1000409754 then "168" else if [Equipement No] >= 1000407443 then 112 else if [Equipement No] >= 1000407440 then 56 else 112, type any),
    #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"WEEK", Order.Ascending}, {"LINES", Order.Ascending}, {"MACHINE", Order.Ascending}, {"Order", Order.Ascending}}),
    #"Grouped Rows" = Table.Combine(Table.Group(#"Sorted Rows", {"MONTH", "WEEK", "Equipement No"}, {{"All", each Table.FromColumns(Table.ToColumns(_) & {{List.Count(List.Distinct(_[Order]))} & List.Repeat({null}, Table.RowCount(_)-1)}, Table.ColumnNames(_) & {"Breakdowns count"})}})[All]),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"MONTH", "WEEK", "LINES", "MACHINE", "Equipement No", "Order", "Breakdowns count", "Actual work", "Breakdown and Hot Call", "Breakdown Duration", "Nb of hours/week"}),
#"Grouped Rows1" = = Table.Combine(Table.Group(#"Grouped Rows", {"MONTH", "WEEK", "Equipment", "Order"}, {{"All", each
let
cn = "Breakdown duration", 
a = Table.RemoveColumns(_, cn)
in
Table.FromColumns(Table.ToColumns(a) & {{Table.Column(_,cn){0}} & List.Repeat({null}, Table.RowCount(a)-1) }, Table.ColumnNames(a) & {cn})
}})[All])
in
    #"Grouped Rows1"

[Vijay_A_Verma]

See the below sample code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rc1BCsAgDETRqwTXLiZqNDmLeP9r2FIIXQgW6WIWHx5M74FDfAagoEpmIrraGBlqBXeOuIDZFYr8QFLDlpgdE3EiWo9JfR21NVEnnHhHTPUTGRM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MONTH = _t, WEEK = _t, Equipment = _t, Order = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MONTH", Int64.Type}, {"WEEK", Int64.Type}, {"Equipment", Int64.Type}, {"Order", Int64.Type}}),
    #"Grouped Rows" = Table.Combine(Table.Group(#"Changed Type", {"MONTH", "WEEK", "Equipment"}, {{"All", each Table.FromColumns(Table.ToColumns(_) & {{List.Count(List.Distinct(_[Order]))} & List.Repeat({null}, Table.RowCount(_)-1)}, Table.ColumnNames(_) & {"Breakdowns count"})}})[All])
in
    #"Grouped Rows"