Log In
Register
Home
February 19, 2023
Offline
Dear All,
I would really appreciate your help.
I have a file; the data is imported via connection only in Power Query. I have many columns (Text and monthly data) but most important ones are the monthly columns. I want to have a custom column for YTD, which will sum up all these columns. Then I want to have another YTD column where it will divide the YTD value by the no of months I have and multiply by 12.
So, suppose we have two months data (April and May).
April = £1,000
May - £3,000
I want a custom column for YTD and the result should be = £4,000
Another custom column for Forcast and the result should be = (£4,000/2) x 12 = £24,000
and when I have in the next month 3 months data (April, May and June)
It should sum up all three months, and for the forecast, it should divide by 3 and multiply by 12 automatically.
Your help would be really appreciated.
Please see the attached picture.
Moderators
January 31, 2022
Offline
See if the attached example does what you need. By the way, it assumes you have one text column and all others contain the monthly data.
February 19, 2023
Offline
Hi Riny,
Many Thanks for your reply and for coming up with the solution.
Yes, this is exactly what I want, but on my file, it's not working. I managed to understand all the steps but 2nd step "Data" (what is happening in this step) and the 3rd step "Count", I could not apply properly (not sure what -1 means).
Could you please have a look at the attached file?
Moderators
January 31, 2022
Offline
Data is nothing more than the "TransformColumnType" step renamed to something more friendly. I used the step name later on when I do a Merge. but now I changed after seeing your file, to make it more dynamic.
Then the Count step is a manually added step that counts the number of columns int he table and deducts 1 to not count the first column. Since your table has 9 text columns you need to deduct 9 here.
And the grouping need to be done on column that contains the unique identifier for each row, so I changed the Name column as a n example.
See attached. come back her if you get stuck.
I didn't load the table back to Excel as the file would become to big to upload here.
Trusted Members
October 18, 2018
Offline
crossposted: https://chandoo.org/forum/thre.....lue.51358/
Please read: https://excelguru.ca/a-message.....s-posters/
The following users say thank you to Alan Sidman for this useful post:
Riny van EekelenFebruary 19, 2023
Offline
Thank you for your explanation and for providing the solution. It is working exactly the way I wanted.
Sorry I cross-posted this question at chandoo as well. I have provided your solution and explanation, so no one else will spend time resolving my query.
February 19, 2023
Offline
I have another query related to the same file, but I have added two more columns this time. Column A is for the FY and column E is for lookup value. There is another sheet where the lookup formula will look and pick the values from.
What I am looking to do is via Power query - first in column A search for the most recent year (in this case 22-23 is the most recent year) and then perform the vlook up only for this particular year and not for other years.
Is this possible in Power Query?
Your help would be really appreciated.
Moderators
January 31, 2022
Offline
I guess that's possible, but to save me from having to guess how your tables and data are structured and exactly what to look-up from where, please upload another file with let's say 50 rows of representative date (though non-confidential) with the expected results in it.
February 19, 2023
Offline
Thank you Riny.
Please see the attached file with the expected results.
Moderators
January 31, 2022
Offline
I like to keep thing simple. And because you don't work with real dates, I added a column to the lookup table for the year "22-23". Then you can easily do a merge based on the Year and Acc columns from the Trend table,
See if you can follow the steps in the attached file.
February 19, 2023
Offline
The problem is the Lookup table values can belong to any of the FY, e.g: 20-21, 21-22, 22-23. So we cannot allocate FY to it by creating a new column for FY. I am keen to avoid this step.
Is there any other way possible?
Moderators
January 31, 2022
Offline
Sorry, but I don't understand. I merely followed your example. How would you do the lookup in Excel for, let's say two out of three years? Perhaps you can give a more complete example that contains all possible situations.
February 19, 2023
Offline
Thank you for your reply and my apology for the late response.
I have now understood the file is working correctly as per my need, but there is one problem. When more data is added to the sheet, we will have 23-24 and 24-25 and so on.
I want to make it dynamic, so it picks the latest FY dynamically and then applies the lookup but the data for other years should remain there in this case for year 22-23.
For example, we are in FY 22-23 and pulling the data using the query created by you successfully and now we are in 23-24; the lookup should happen dynamically for FY 23-24 and stop for 22-23, and whatever the data it has pulled for year 22-23 all rows should stay.
I hope the above makes sense.
Thank you very much for looking into my problem and putting your time and effort into finding the solution.
Moderators
January 31, 2022
Offline
Okay! Since you work with texts rather than real dates you can't just scan the Year column for the "latest date". I added a small query that just takes the Year column, sorts it in descending order and then drills down to the entry in first row, creating a variable called MaxYr.
I removed the Yr column(that I had manually added in Excel) from the look-up table but now dynamically add it in PQ based on the variable MaxYr. Then the other query works the same.
So, when you have Year "23-24" in the data, it will be picked up as the MaxYr. It will add the column Yr to the look-up table filled with "23-24" and merge (i.e. lookup) only rows for the Year "23-24".
Perhaps not the most elegant solution, but it works and it's easy to follow.
1 Guest(s)
