Delete duplicates or pivot using dax

[nicolasvc]

I have this table where I calculate the column "new" using dax, adding the offer value for each week and center, as you can see, all the rows that repeat week and center have the same value. But I would like to pivot or eliminate duplicates, the problem is that when using power query it does not show the column that I have calculated.

Center	week	offer	new
1	21	-22	220
1	21	242	220
3	22	0	20
3	22	20	20

So how can I have a table like this?

Center	week	new
1	21	220
3	22	20

[V-pazhen-msft · Accepted solution]

@nicolasvc 

Instead having complex transformation steps in power query. You could create a table using dax, this is also faster in terms of loading speed.

Table 2 = SUMMARIZE('Table (2)',[Center],[week],[new])

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Pragati11]

HI @nicolasvc ,

I am not sure on the DAX expression that you have created.

Is it  a column or a measure?

If OFFER is a column than create it in Power Query instead of using DAX. In that way you will be able to see that in Power Query and remove duplicates.

Thanks,

Pragati

[nicolasvc]

Is a column I calculated it doing something like this:

New= CALCULATE(SUM(Table[offer]),ALLEXCEPT(Table,Table[week],Table[Center]))

But I did it in DAX because I couldn't find a way to do it in power query.