Log In
Register
Home
November 7, 2024
Offline
Hi Experts,
I am currently learning Excel reporting with the help of Power Query, where I am stuck in a project. I have 2 tables as below. I did try myself (I append both, add Calendar create Parameter, Custom columns) but failed.
1st is Previous years data (current year partial).
2nd is Current year data (partial) and future years data.
Now, I’m trying to create custom columns to calculate LostValue, RepeatValue, and NewValue based on the selected year from a slicer. However, I’m encountering errors in my custom columns, and I’m not sure how to reference the selected year correctly.
Attached sample file have the display what kind of working I am trying to do. I will be really grateful if someone can tell me with steps. is this possible to achieve by using Power Query (I am not working on Power BI).
Thank you so much in advance for your precious time and help.
See Answer
Moderators
January 31, 2022
Offline
The file you intended to attach didn't come through. Please try again and don't forget to press 'Start Upload' before you submit your reply.
November 7, 2024
Offline
Hi Riny van Eekelen,
Really sorry I did attached but don't know why the attachment missed to attach. here is the sample file.
Thank you so much.
Moderators
January 31, 2022
Offline
I see what you want to do. Give me some time to think about it.
November 7, 2024
Offline
Thank You Riny for thinking about my request. I hope we can figure something out to solve it.
Moderators
January 31, 2022
Offline
I created something in PQ. Let me know if that's what you had in mind. Beware that I determined the current year (=dropdown) status based on comparing the current year values with those of the previous year.
See attached.
November 7, 2024
Offline
Thank you so much Riny van Eekelen for your precious time and help!
Your file about comparing current and previous year values with Status in PQ is really enlightening. (I learned from this how can I compare) I appreciate the dropdown you created,
but I am looking to implement a slicer for automatic data refresh instead.
Also, I need to adjust the Pivot table to show “Customer” “New” “Repeat” and “Lost” instead of “Previous,” “Current,” and “Status.” (if it is possible)
Once again Thank you so much.
Moderators
January 31, 2022
Offline
Can't really see how a pivot table and a slicer can achieve what you want in that exact format.
November 7, 2024
Offline
Thank you so much for your precious time and help, I actually need to get pivot table like below refreshed by Year slicer from PQ report. if this can be possible.
Lost = which customer is not in current year (selected in slicer) but was available in previous year then (previous year value)
Repeat = in both years (current and previous)
New = which was not available in previous years (but available in current selected year)
| Customer | Lost | Repeat | New |
| A | 110 | ||
| B | 100 | ||
| C | 50 |
Moderators
January 31, 2022
Offline
Perhaps Power Pivot can do this. Am on my way out so can't spend time on it right now. Sorry.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Not sure you need a Pivot Table for the data, but then I don't know how much data you do have. Attached is an example of using a Pivot Table for the slicer only and just use the filtered table value in ordinary formulas.
Check if the approach is usable for you.
Do check out the Use Excel Slicer Selection in Formulas • My Online Training Hub blog article for tips on how to use slicer selection in formulas.
Br,
Anders
November 7, 2024
Offline
Wow Anders interesting, i will definitely read and will love to learn from this and your shared sample. how can i do this with the help of power query.
Thank you so much, i will come back with the final file if i achieved the goal, i hope with help of Riny and You i will reached.
Thank so much both of you for your precious time and precious help.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
I have made another variant where I add some helper columns to the table, which in my view makes it easier to follow the logic for the different status values. I also use the UNIQUE function to get the list of customers, if you don't use Excel 365, 2021 or 2024 that will not work for you.
Br,
Anders
Answers Post
November 7, 2024
Offline
Hi Anders,
I apologize for my late response. I was dealing with some family matters that required my attention. Your support meant a lot to me, and I sincerely thank you for your precious time and help. Thank you so much {Love}
I also have a question: if I have a dataset with around 20,000 rows and 16 columns that continues to grow month by month, will the file size increase significantly due to the formulas I’m using?
Thanks again for your assistance!
VIP
Trusted Members
December 7, 2016
Offline
Hello,
The document file size will of course grow with new data added. There are ways to minimize the growth, for example this article about Reduce the file size of your Excel spreadsheets.
/Anders
1 Guest(s)
