cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sami_77
Frequent Visitor

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

 

Dataquestion.jpg

 

1 ACCEPTED SOLUTION

Accepted Solutions
Sami_77
Frequent Visitor

Re: How to modify row level data based on column value

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"

 

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

Re: How to modify row level data based on column value

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/

 





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


Sami_77
Frequent Visitor

Re: How to modify row level data based on column value

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

Sami_77
Frequent Visitor

Re: How to modify row level data based on column value

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"

 

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors