Forum Discussion
Monthly CSV Report
Hi QuintinC ,
I have tried some transformation from the excel file you shared and attacg .pbix file here. I made a copy with just one change of 'Date report generated' to change to June.
Key things I could accomplish are:
1. Merge both file from Folder.
2. Retrieve date of report generation to a new column. A custom column is added and fill down function copies date to null rows.
I have put comments on key transformation steps, just hover the mouse and you will see it.
Finally, made a sample visual to show you how the data look like.
This may not be the exact transformation needed by you, but it would show you power of power query in such transoformation. Thing is, we need to find transform logic based on our use case and try to use relevant functions available.
https://drive.google.com/drive/folders/1C3NfpE-rvX9HQN4ejSp7Jhkpa2H3B9K3?usp=sharing
Hope it helps.
Hi QuintinC ,
In additon to merging you need to transform the data to make it usable. First to tell you, the problem you face is quite normal and can be solved with Power query definitely. Solution or say, the type of transformation shall depend on your actual data because every use case is somewhat specific to user.
If you have chance to read through this book, you will learn such transformation in depth:
https://www.amazon.in/Data-Monkey-Guide-Language-Excel/dp/1615470344
At least just read through chapter index and you will learn key terminology to use during your search on google.
If you still have difficulty to find the solution, please post couple of sample sheets without sensitive data or just keeping dummy data and one of us can help you out!
I ordered the book you suggested online and will hopefully get it this weekend. I created a dummy file in case you are interested. Essentially I would be generating these a few times per month at a minimum of at least once per month. Each file would look like this with the exception of additional or fewer rows depending on if we lost a grouping recently. The other difference is the month name of the column. Looking for a way to create a nice report to show changes in a chart throughout the year and show trends utilizing the totals and being able to break them down by different filters.
https://drive.google.com/file/d/1-VuMyaKZU10a8qhuXt-5WPuPz9iU1dnG/view?usp=sharing
Hi QuintinC ,
I have tried some transformation from the excel file you shared and attacg .pbix file here. I made a copy with just one change of 'Date report generated' to change to June.
Key things I could accomplish are:
1. Merge both file from Folder.
2. Retrieve date of report generation to a new column. A custom column is added and fill down function copies date to null rows.
I have put comments on key transformation steps, just hover the mouse and you will see it.
Finally, made a sample visual to show you how the data look like.
This may not be the exact transformation needed by you, but it would show you power of power query in such transoformation. Thing is, we need to find transform logic based on our use case and try to use relevant functions available.
https://drive.google.com/drive/folders/1C3NfpE-rvX9HQN4ejSp7Jhkpa2H3B9K3?usp=sharing
Hope it helps.
Thank You! I will play with this for a while and see what I can do. I was able to generate a different report that was slightly more friendly and sophisticated however it should have the same problems so I will work with this for now and see if I can gain some insight. Thank You!
I have managed a lot of progress. I have been able to insert 3 years with of data and it all works well. Now on the visual side I am struggling. The image below describes my problem for all of my visuals. I should never be anywhere over 10,000 for any of my grand totals ever. As an example 1023 had 51 one month and then 51 the the next meaning thy still have 51. The entire purpose of my project is to be able to show growth trends of different groups based on Number, Group, and type.