Forum Discussion
How to modify row level data based on column value
Hi, I need to modify row level data in my datatable, and need help how to solve this in Power BI query editor... The case is described in the below datatable snapshot, with picture of original d...
I was able to reach the desired query outcome with the solution example provided by Excelerator BI in youtube:
https://www.youtube.com/watch?edufilter=NULL&v=xN2IRXQ2CvI
Below is my complete query code which provided the correct solution.
let Source = Excel.Workbook(File.Contents("C:\Users\usvaini\OneDrive - BUNGE\PowerBI\Harjoitukset\OEE model tests\Datareplace.xlsx"), null, true), notsupp_Test_Table = Source{[Item="notsupp_Test",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(notsupp_Test_Table,{{"Date", type date}, {"Shift ID", Int64.Type}, {"Productkey", Int64.Type}, {"Manhours", type number}, {"packcnt", Int64.Type}, {"Line", type text}, {"downtime", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Productkey", Order.Ascending}, {"Date", Order.Ascending}, {"Shift ID", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1), #"Renamed Columns" = Table.RenameColumns(#"Added Index1",{{"Index.1", "previous"}}), #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"previous"}, #"Renamed Columns", {"Index"}, "Renamed Columns", JoinKind.LeftOuter), #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Index", "previous"}), #"Expanded Renamed Columns" = Table.ExpandTableColumn(#"Removed Columns", "Renamed Columns", {"Date", "Shift ID"}, {"Renamed Columns.Date", "Renamed Columns.Shift ID"}), #"Renamed Columns1" = Table.RenameColumns(#"Expanded Renamed Columns",{{"Renamed Columns.Shift ID", "previous ShiftID"}, {"Renamed Columns.Date", "Previous shift date"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Shift ID_fixed", each if [Line]=null then [previous ShiftID] else [Shift ID]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Date_fixed", each if [Line]=null then [Previous shift date] else [Date]), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Previous shift date", "previous ShiftID", "Date", "Shift ID"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Date_fixed", "Shift ID_fixed", "Productkey", "Manhours", "packcnt", "Line", "downtime"}), #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",null,0,Replacer.ReplaceValue,{"Manhours"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"downtime"}), #"Grouped Rows" = Table.Group(#"Replaced Value1", {"Date_fixed", "Shift ID_fixed", "Productkey"}, {{"packcnt", each List.Sum([packcnt]), type number}, {"Manhours", each List.Max([Manhours]), type number}, {"downtime", each List.Max([downtime]), type number}, {"Line", each List.Max([Line]), type text}}), #"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"Date_fixed", Order.Ascending}, {"Shift ID_fixed", Order.Ascending}}), #"Reordered Columns1" = Table.ReorderColumns(#"Sorted Rows1",{"Date_fixed", "Shift ID_fixed", "Productkey", "packcnt", "Manhours", "Line", "downtime"}) in #"Reordered Columns1"
amitchandak
Super User
Super UserYou can go to data transformation or edit query mode and remove or filter rows based on conditions
Steps are same as given in
https://www.myexcelonline.com/blog/remove-rows-using-power-query/
Hi,
removing the rows with 'Line' value equal to "not supplied" isn't the issue.
The problem is how to add the 'packcnt' values from those rows to previous row with matching 'Productkey' value.
What would work also is following logic:
For rows where 'Line' value equals "not supplied",
=> Replace 'Date' and 'Shift ID' column values from previous row with matching 'Productkey'.
After this I would group rows by 'Date', 'Shift ID' and 'Productkey' columns with aggregation of Summing the 'packcnt' values.
But how to apply the above logic....
I was able to reach the desired query outcome with the solution example provided by Excelerator BI in youtube:
https://www.youtube.com/watch?edufilter=NULL&v=xN2IRXQ2CvI
Below is my complete query code which provided the correct solution.
let Source = Excel.Workbook(File.Contents("C:\Users\usvaini\OneDrive - BUNGE\PowerBI\Harjoitukset\OEE model tests\Datareplace.xlsx"), null, true), notsupp_Test_Table = Source{[Item="notsupp_Test",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(notsupp_Test_Table,{{"Date", type date}, {"Shift ID", Int64.Type}, {"Productkey", Int64.Type}, {"Manhours", type number}, {"packcnt", Int64.Type}, {"Line", type text}, {"downtime", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Productkey", Order.Ascending}, {"Date", Order.Ascending}, {"Shift ID", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1), #"Renamed Columns" = Table.RenameColumns(#"Added Index1",{{"Index.1", "previous"}}), #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"previous"}, #"Renamed Columns", {"Index"}, "Renamed Columns", JoinKind.LeftOuter), #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Index", "previous"}), #"Expanded Renamed Columns" = Table.ExpandTableColumn(#"Removed Columns", "Renamed Columns", {"Date", "Shift ID"}, {"Renamed Columns.Date", "Renamed Columns.Shift ID"}), #"Renamed Columns1" = Table.RenameColumns(#"Expanded Renamed Columns",{{"Renamed Columns.Shift ID", "previous ShiftID"}, {"Renamed Columns.Date", "Previous shift date"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Shift ID_fixed", each if [Line]=null then [previous ShiftID] else [Shift ID]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Date_fixed", each if [Line]=null then [Previous shift date] else [Date]), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Previous shift date", "previous ShiftID", "Date", "Shift ID"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Date_fixed", "Shift ID_fixed", "Productkey", "Manhours", "packcnt", "Line", "downtime"}), #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",null,0,Replacer.ReplaceValue,{"Manhours"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"downtime"}), #"Grouped Rows" = Table.Group(#"Replaced Value1", {"Date_fixed", "Shift ID_fixed", "Productkey"}, {{"packcnt", each List.Sum([packcnt]), type number}, {"Manhours", each List.Max([Manhours]), type number}, {"downtime", each List.Max([downtime]), type number}, {"Line", each List.Max([Line]), type text}}), #"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"Date_fixed", Order.Ascending}, {"Shift ID_fixed", Order.Ascending}}), #"Reordered Columns1" = Table.ReorderColumns(#"Sorted Rows1",{"Date_fixed", "Shift ID_fixed", "Productkey", "packcnt", "Manhours", "Line", "downtime"}) in #"Reordered Columns1"