how do you return the maximum value of a column in a new column in power query

[shneierl]

not sure if this has been asked before but a quick search/goggle and i couldn't find it obviously (probably means my SEO wasn't great). 

However one of my datasets which gets refreshed weekly is latest weeks data can often be misleading as half of its sources don't normally update until the following weeks refresh. Idon't have access to the table that the source data comes from so i can't add a restriction into the code at that point that removes the latest week before exporting the data into my table. therefore i would normally in power pivot create  a custom column that returns the maximum value in the dataset and then a simply column that does a yes no check to see if this value is that maximum then filter out the yes's in the final product.

However the model i'm working on is entirely power query driven as i don't need a data model just lots of different views of the data for various regions (all using a referenced query from the source) without enabling the end user to see data not from their region. i therefore need to replicate the steps i'd normally do in DAX in M

what i have done so far is referenced my source data grouped it by week and created an index of that then merged this index back into the source data (because we need the more detail than just week and i wasn't certain how the index would react to me expanding the data after grouping so went with a more complex but easier to track method.

the issue i have is i can't work out how to keep this new index column and add a column(s) that say what the maximum index value is and is the index value for this row equal to that maximum. I have outlined my expected outcome in the below table, I have reduced the number of columns to show only the key information but there would be many values for each week in the real data I just can't see how this impacts anything as they'd all have the same index value.

date	region	week index	max index	latest week
04/08/2021	north	14	18	no
11/08/2021	south	15	18	no
11/08/2021	south	15	18	no
01/09/2021	central	18	18	yes
01/09/2021	north	18	18	yes
25/08/2021	south	17	18	no

any help would be appreciated even if it is just pointing me in the direction of a previous query that answered the above.

[Anonymous · Accepted solution]

Sorry!  It should be:

= Table.AddColumn(PreviousStepName, "MaxWeek", each List.Max(PreviousStepName[WeekIndex]))

--Nate

[shneierl]

Cheers that removed the error and got the answer I was expecting for the max week and I've been able to manipulate the column into both a yes no check (for certain graphs) and a dynamic age column (so I can have pivots etc highlight latest 4 /6/8 weeks without having to change the filter

[Anonymous]

To get the result you posted:

= Table.AddColumn(PreviousStepName, "MaxWeek", each List.Max([WeekIndex]))

Then:

Table.AddColumn(PriorStep, each if [WeekIndex] = [MaxWeek] then "yes" else "no")

--Nate

[shneierl]

hi Nate,

thanks for this it looks like it should work but i after adding in the code you have put in for the first custom column i am getting an error on each row saying

"Expression.Error: We cannot convert the value 8 to type List. Details: Value=8 Type=[Type]" the error is the same for every row just with a different value to reflect the index value of the row

when i look at the previous step the index is formatted as a whole number i have tried swapping to decimal number but it is still getting the same error.

t

i have posted the exact line of code for this column below can't see anything obviously wrong with it do i need to tell it to feed the column in as a number first?

= Table.AddColumn(#"Changed Type1", "MaxWeek", each List.Max([WeekIndex]))

[Anonymous]

You probably could use something like this: List.Max(table[Date]) and save it in a separate paramter, so it would always pick up the latest date from your dataset. Then you could add conditional column to check if the date in the column is = to the LatestDate paramter. You may need to divide the query into two to avoid cyclical reference error.

If you just want to load just the latest dates, then you can always just filter the date column and select option 'latest'.

regards,

Piotr

[shneierl]

now i feel foolish when i was doing date filters my eyes sw the is lates is before etc but did not see the is not latest option. using the is not latest will exclude the troublesome week from the data for this task. 

However it doesn't solve the underlying analysis i would do with wider projects where i'd say if data is in latest week divide target by 7 and times by distinct count (when comparing daily submissions to see if they have missed any days in a week so far). although in most of these cases i have no issue loading this data into DAX where i normally would create these columns/measures.

at least i can supress this dodgy data without having to go into the qquery every time now.

[Anonymous]

I am not sure if I understand completly what you are trying to do and there might be a better solution, but you can just create another query which would reference the first query and use group by... e.g. date, then in the options for the new column you chose count rows or distinct count rows. this will give you a table with the count per day, then you can merge that table to the other query and when you expand the count column you can do your formulas. 

If you want to just divide the target by 7 if it's in the latest week, why you can't just use the conditional column?