Remove repeated values from cells retaining the row from Power Query

[umamusic]

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

 

[sandy666]

use XL2BB to post representative example and expected result

 

[umamusic]

Here it is-

 

[umamusic]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Product	Region	Sales					Product	Region	Sales
2		A	East	100					A	East	100
3		A	West	50						West	50
4		A	West	25							25
5
6
7
8
9
10
11
12
13
14
Sheet1

 

[sandy666]

use Pivot Table

Product	Region	Sales		Product	Region	Sales
A	East	100		A	East	100
A	West	50			West	25
A	West	25				50

 

[sandy666]

anyway with Power Query and your example

Table1				PQ Table
Product	Region	Sales		Product	Region	Sales
A	East	100		A	East	100
A	West	50			West	50
A	West	25				25

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]

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]

You are welcome & thanks for the feedback
Have a nice day