Remove repeated values from cells retaining the row from Power Query

umamusic

New Member
Joined
Jan 9, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi, I'm new to Power Query and BI. With all experiments, I'm able to get to a sheet/table that can serve as a CSV import file. However, I want to remove row values that are repeating retaining the row.
Ex. From this table
Product Region Sales
A East 100
A West 50
A West 25

I want to get

Product Region Sales
A East 100
West 50
25

Kindly help
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

umamusic

New Member
Joined
Jan 9, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Book1
ABCDEFGHIJKLMNO
1ProductRegionSalesProductRegionSales
2AEast100AEast100
3AWest50West50
4AWest2525
5
6
7
8
9
10
11
12
13
14
Sheet1
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

use Pivot Table
ProductRegionSalesProductRegionSales
AEast100AEast100
AWest50West25
AWest2550
 
Solution

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
anyway with Power Query and your example
Table1PQ Table
ProductRegionSalesProductRegionSales
AEast100AEast100
AWest50West50
AWest2525

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    Distinct1 = Table.Distinct(Table.SelectColumns(Index,{"Product", "Index"}), {"Product"}),
    Distinct2 = Table.Distinct(Table.SelectColumns(Index,{"Region", "Index"}), {"Region"}),
    Join1 = Table.ExpandTableColumn(Table.NestedJoin(Distinct1,{"Index"},Distinct2,{"Index"},"Table",JoinKind.FullOuter), "Table", {"Region", "Index"}, {"Region", "Index.1"}),
    Join2 = Table.ExpandTableColumn(Table.NestedJoin(Join1,{"Index.1"},Table.SelectColumns(Index,{"Sales", "Index"}),{"Index"},"Table",JoinKind.FullOuter), "Table", {"Sales", "Index"}, {"Sales", "Index.2"}),
    TSC = Table.SelectColumns(Join2,{"Product", "Region", "Sales"})
in
    TSC
 

umamusic

New Member
Joined
Jan 9, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Thanks much. I couldn't try the 2nd coding solution in Power Query as I'm getting errors. However, I managed it with Pivot. As I'm building a model, I didn't want to use Pivot. However, for now this seems to be the best solution that I can understand well and implement. Looking forward to learning more. Thank you very much
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
You are welcome & thanks for the feedback
Have a nice day
 

Watch MrExcel Video

Forum statistics

Threads
1,126,986
Messages
5,621,992
Members
415,873
Latest member
fuulhouse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top