Forum Discussion
How to get a MAX date between two columns in a power query
Hi,
I want to create a formula where the formula is each [Anchor Date] this is another table in the column instead of [todaysDateCL] - MAX( "System Entry Date / Inception Date", "Client Due Date / UW Due Date"] however the entry gives me an error says check syntax Max isnt spelled correctly. Thanks.
In M code, the Max between two columns
=List.Max({[Date1]}&{[Date2]})
Hence, in your case it would be
=List.Max({[#"System Entry Date / Inception Date"]}&{[#"Client Due Date / UW Due Date"]})
Since the column name is containing a "/", hence you will need to enclose it in [ ] and precede by #. Spaces are OK in column name.
If ever you commit a mistake, just insert a custom column and just click on your column name, it will show you the right syntax.
{ } - This converts the value of that record from that column to a list.
& is joining two single value list to prepare a list with 2 values. (You can also do it in various ways like you can also use List.Union but & is shorted)
To get Max in PQ, List.Max is the function which operates on lists only. Hence, I had to convert column record to list.
3 Replies
- Vijay_A_Verma
Most Valuable Professional
In M code, the Max between two columns
=List.Max({[Date1]}&{[Date2]})
Hence, in your case it would be
=List.Max({[#"System Entry Date / Inception Date"]}&{[#"Client Due Date / UW Due Date"]})
Can you explain why the # & }please ? thank you !
- Vijay_A_Verma
Since the column name is containing a "/", hence you will need to enclose it in [ ] and precede by #. Spaces are OK in column name.
If ever you commit a mistake, just insert a custom column and just click on your column name, it will show you the right syntax.
{ } - This converts the value of that record from that column to a list.
& is joining two single value list to prepare a list with 2 values. (You can also do it in various ways like you can also use List.Union but & is shorted)
To get Max in PQ, List.Max is the function which operates on lists only. Hence, I had to convert column record to list.
