Compare 2 Rows in Power BI and Delete Matched Rows

tejapowerbi · Dec 1, 2018

Please see below scenarios and provide your suggestions,
My data source is in Oracle to power bi and as per my requirements I need to compare order no rows, if order number has .1 then I need to find order number without .1 and delete both rows. Does any one has any suggestion?

[TABLE="class: MsoNormalTable, width: 188"]
<tbody>[TR]
[TD="width: 71"] Order No[/TD]
[TD="width: 71"] Match[/TD]
[TD="width: 109"] Result[/TD]
[/TR]
[TR]
[TD="width: 71"]

123

[/TD]
[TD="width: 71"] Yes[/TD]
[TD="width: 109"] Delete[/TD]
[/TR]
[TR]
[TD="width: 71"]

123.1

[/TD]
[TD="width: 71"] Yes[/TD]
[TD="width: 109"] Delete[/TD]
[/TR]
[TR]
[TD="width: 71"]

555

[/TD]
[TD="width: 71"] No[/TD]
[TD="width: 109"] Keep it[/TD]
[/TR]
[TR]
[TD="width: 71"]

444

[/TD]
[TD="width: 71"] Yes[/TD]
[TD="width: 109"] Delete[/TD]
[/TR]
[TR]
[TD="width: 71"]

444.1

[/TD]
[TD="width: 71"] Yes[/TD]
[TD="width: 109"] Delete[/TD]
[/TR]
</tbody>[/TABLE]

Matt Allington · Dec 1, 2018

How about you replace .1 with nothing in the order number column, then group by order number and count the number of rows. Anything with 2 has to be deleted. Join this back on the version of the order number with the .1 removed and do a left anti-join.

tejapowerbi · Dec 1, 2018

Hi Matt ,thank you so much for attending my question.Can you please explain me in details?

Matt Allington · Dec 2, 2018

Can you please share a sample workbook with realistic data and I will show you.

tejapowerbi · Dec 3, 2018

Hi Matt,
attached is the file for your consideration.I do not see attachment options so i am pasting the data set. Thanks

Invoice No Result
123.1 Delete
123 Delete
555 keep it
444 Delete
634.2 keep it
634 keep it
444.1 Delete

tejapowerbi · Dec 3, 2018

Hi Matt,
i have tried to replace .1 with nothing ( i have used null key word) but nothing happened.Do you have any suggestion for me?
Thanks
Tejas

horseyride · Dec 4, 2018

This is what Matt had in mind

Code:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order No", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",".1","",Replacer.ReplaceText,{"Order No"}),
Grouped = Table.Group(#"Replaced Value", {"Order No"}, {{"Count", each Table.RowCount(_), type number}}),
#"Merged Queries" = Table.NestedJoin(#"Replaced Value",{"Order No"},Grouped ,{"Order No"},"Grouped ",JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Grouped ", {"Count"}, {"Count"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table1", each ([Count] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count"})
in #"Removed Columns"

tejapowerbi · Dec 4, 2018

Thank you so much Horseyride and Matt.I got it and my problem has been solved. ?

Compare 2 Rows in Power BI and Delete Matched Rows

tejapowerbi

New Member

Excel Facts

Matt Allington

MrExcel MVP

tejapowerbi

New Member

Matt Allington

MrExcel MVP

tejapowerbi

New Member

tejapowerbi

New Member

horseyride

Board Regular

tejapowerbi

New Member

Similar threads

Forum statistics

Share this page

Compare 2 Rows in Power BI and Delete Matched Rows

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

Board Regular

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock