How to modify row level data based on column value

[Anonymous]

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 data and  picture of the desired data after the modification formula. The problematic datarows have missing data in 'Manhours' and 'downtime' columns, and the 'Line' column equals text-value "not supplied".

I need to handle these rows (=rows with 'Line' column equal to text-value "not supplied") so that 'packcnt' value is added to the 'packcnt' value of the previous row with matching 'Productkey' column value. In addition, the datarow with missing values must be removed (this one is easy with filtering).

Can someone guide me to a working solution with this, would be really appreciated!

link to the datatable (excel): https://1drv.ms/x/s!Al0QMrDNxhFZlZws9uPlrlVGKW7PGg?e=zLom5g

[amitchandak]

You 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/

[Anonymous]

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.... 

[Anonymous · Accepted solution]

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"