Forum Discussion
Monthly CSV Report
Newbie here! I am looking for directions to create a graph/visuals for a monthly report that I run. It's an indicator of Membership Loss from the one year prior of that exact date. I have multiple issues. One is I have a big disclaimer on top that needs to be transformed so I can have actual headers so it's not automatic. Second, being one of the columns has a different name per the month we are in. And another issue is without the header there is no way to distinguish what report it is pulling from. (Expire Date is useless/unrelated). I want to show a total trend of each specific unit and it's numbers based on other reports that I have. I tried following this guide and followed a comment changing it so it works with CSV. https://sqlitybi.com/loading-multiple-excel-files-from-a-folder-in-power-bi/
However, this basically merged all of the excel sheets into one giant one and did not solve the column issue with being different names.
On the other hand, I tried another way by having a workbook. However, this did not allow me to use them as a group. as they had all the different units separate and I couldon't find a way to make it work. Does anyone know of any forum posts that could assist?
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.
6 Replies
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=sharingHi 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.
Alright I will see what I can do!
