list max - function doesn't work properly

[RobertO995]

Hello all,

I need your help with List.Max in PowerBi. I'm totally new in M language and even simply things make a lot of troubles for me.

I have separated columns with date and year. For example: one column is 01/01/2018 and second is just 2018. In my table I have dates from 2017 to 2019 and I want to create another column with the maximum year in every row - so in this case, every row (in dates from 2017 to 2019) will be 2019, in the next year it will be 2020 (this is because I'm using operational years instead of calendar).

I guess, I need to use List.Max function, but got some issues with that. 

If I try List.Max([Year column]) - I received "Cannot convert 2019 value to List type,

If I try List.Max[Year column] - I received "Cannot use access to filed for type Function"

The only way, I received what I want was to copy the whole table, match column with year, click 'convert to list' button, and then add another column and use List.Max function, but I have a few questions:

1) will this list refresh automatically if the new dates will appear?

2) Is there simplier way to receive max year in another column in every row?

Thanks in advance!

[OwenAuger · Accepted solution]

@RobertO995

You can refer to the Year column of the table using the syntax PreviousStepName[Year], where PreviousStepName is the name of the step just before adding the Custom Column.

So in the Custom Column dialog box, the code would look like:

=List.Max( PreviousStepName[Year] )

Regards,

Owen

[v-juanli-msft · Accepted solution]

Hi @RobertO995

You could take OwenAuger's advice, as tested, add a custom column using the following format 

#"Changed Type" is the previous step

List.Max(#"Changed Type"[Year])

or write code in Advanced editor

= Table.AddColumn(#"Changed Type", "Custom", each List.Max(#"Changed Type"[Year]))

When i add year 2020 in my original table, then refresh from power bi desktop, it would automatically change to 2020 from 2019.

Best Regards

Maggie

[OwenAuger · Accepted solution]

@RobertO995

You can refer to the Year column of the table using the syntax PreviousStepName[Year], where PreviousStepName is the name of the step just before adding the Custom Column.

So in the Custom Column dialog box, the code would look like:

=List.Max( PreviousStepName[Year] )

Regards,

Owen