New Member
December 28, 2023
Hi, I am quite new to all this.
What I am trying to do is to convert a list with daily entries into a list with monthly entries, removing all the days but just keep the last day of the month.
The problem I have is that the dates in the daily lists are not perfect. For example January 2009 ends on the 30.1.2009 instead of the 31.1.2009. Sometimes May ends on the 30st, sometimes on the 31st, and so on.
I have tried the following but it didn't work:
1) Add a new column without the days in the date: So all january dates are 01/2008, Feb dates are 02/2008 and so on.
Then I tried to compare the current cell with the cell above. When it is not the same, then the cell above should be the last day of the month.
I tried this with the following formula :
= if [xy] = Table.Column([#"USDEUR"], "[xy]"){[Index] - 1} then true else false
xy is the name of the column where I want the cells to compare. And USDEUR is the name of the query. Index is the name of the index column that ChatGTP wanted me to create.
The error message I get is : Expression.Error: The field 'USDEUR' of the record wasn't found.
Details:
FxDate=18/01/2008
FxToEUR=1.46168
xy=01/2008
Index=16
I do not know how to proceed. Perhaps someone can help me out. Thanks in advance.
Moderators
January 31, 2022
My guess is that you have daily exchange rates and you want to create another list that contains all latest rates for each month. I can probably think of other ways to achieve that but would need to see your data.
Best to upload your file with the exchange rates and query(ies) that you created already, but perhaps the attached file can help you on your way.
1 Guest(s)