Forum Discussion
svishwanathan
Helper III
9 years agoPower Query Syntax
Hello I am trying to write some queries in M and have not found much success. I have a column with years ranging from 1985 to 2026. I want to find the min year and also the relative year (whi...
- 9 years ago
Sure:
MarcelBeug
Community Champion
9 years agoList.Min({[Calendar.Year]}) just returns the minimum Calendar.Year of each rows Calendar.Year, in other words: just the value of each rows Calendar.Year value.
Instead, you must use the minimum value of the entire table column.
Example query:
let
Source = #table(type table[Calendar.Year = Int64.Type],List.Zip({{1985..2020}})),
MinYear = List.Min(Source[Calendar.Year]),
#"Added Custom" = Table.AddColumn(Source, "WrongCode", each [Calendar.Year] - List.Min({[Calendar.Year]})),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "CorrectCode", each [Calendar.Year] - MinYear)
in
#"Added Custom1"- svishwanathan9 years ago
Helper III
Thanks Marcel
I will try this but I was wondering that instead of getting the Advanced Editor and tweaking hte code, can i write it as custom column
when I edit my query..
Please let me know if I am not using the correct language and using M incorrectly here
Swati- MarcelBeug9 years ago
Community Champion
Sure:
- svishwanathan9 years ago
Helper III
Thanks
I wouldnt go very far without help from you and others
Btw I saw one of your videos even today on Youtube
Thanks to you and all others who are so patiently leading us along