Sort Column in Power Query

[Siboska]

Hi, 

My question is very straight forward. 
How can I replicate the following DAX calculated column in Power Query:

SortColumn = CALCULATE(MAX(HistoricalDatesInitiatives[TestSort]),ALLEXCEPT(HistoricalDatesInitiatives,HistoricalDatesInitiatives[SnapShotName))

I need the column for sorting another column but because I only can figure out how to do it in DAX I get the Circular dependency error. 

[wdx223_Daniel]

NewStep=let a=Table.Buffer(Table.Group(HistoricalDatesInitiatives,"SnapShotName",{"n",each List.Max([TestSort])})) in Table.AddColumn(HistoricalDatesInitiatives,"SortColumn",each a{[SnapShotName=[SnapShotName]]}[n])

[Anonymous]

Hi @Siboska ,

Is Pete or Imke's answer helpful? If so, please consider marking the reply as the solution, this will help future searchers of similar questions in the forum to find the answer faster. Thanks in advance!‌‌

If the problem is still not resolved, you can continue to add details to the question below and feel free to contact us.

Best Regards,
Gao
Community Support Team

[ImkeF · Accepted solution]

Hi @Siboska ,
if you group on columm "SnapShotName" you can use the MAX function on the "TestSort"-column.
Then add the "All Rows"-aggregation to return all the other columns of the table as well.
Expand all columns afterwards:

[BA_Pete]

Hi @Siboska ,

*EDIT* The below will just give you the rows that contain the max value of [TestSort] per each [SnapShotName] group. If you actually need all the rows, but just need the max group value duplicated over the rows, then @ImkeF 's solution below is the correct one. 👍

Without an example of your source data I can't give you the exact code, but the general steps would be:

1) Select [SnapShotName] column in PQ and go to Home tab > Group By.

2) In the Group By dialog, call the aggregate column 'data' and use the All Rows operator.

3) Create a new custom column with the following code:

Table.Max([data], "TestSort")

 4) Expand the new Record column, selecting the columns that you want to reinstate to the table.

Pete