Forum Discussion
Calculate weekly usage of a printer
Hi all,
Every week I receive an email from the printer with an attached csv file which contains the print reports divided for each user. I save this file in a sharepoint folder which is where power query takes the data from.
The problem is that the report contains the totals from the first day of use of the printer because, unfortunately, I have no way to reset the printer data after each export.
I need the weekly usage of the printer. Is there a way in power query to load only the difference between the last file and previous?
Thank you
Ah, cool. I should have looked more carefully at your screenshot.
-1- Sort your data by [Data e ora] ascending.
-2- Add an index column starting from zero and call it [Index0]: Add Column tab > Index Column (dropdown) > 'From 0'
-3- Add an index column starting from one and call it [Index1].
-4- Go to the Home tab > Merge Queries. Select the same query as the second table to merge (you're going to merge the table to itself).
-5- Left Outer merge on Table1[Index0] = Table2[Index1]
-6- Expand [Data e ora] and [Stampa totale] from the nested merge column.
You now have the previous report date and the previous report meter read on each weekly row, so you can now get the meter change value and the number of days over which this change occurred.
There's a few other tidying bits you'll want to do with column names (Start Date/End Date, Start Read/End Read etc.) but the basic principle of what you need to do is here.
Pete
3 Replies
Hi AleMotta84 ,
If you're keeping each file in a SharePoint folder then the best thing to do would be to connect to that folder using the SharePoint Folder connector in Power Query.
Once you select the SP folder with all your files in, you can choose to 'Combine & Transform Data' to append all those files together into one:
Once you have them all in a single table, you can then use either index + self-merge or something more technical to get the previous/next value on each row.
Pete
Hi BA_Pete,
Thanks for the quick reply!
BA_Pete wrote:Hi AleMotta84 ,
If you're keeping each file in a SharePoint folder then the best thing to do would be to connect to that folder using the SharePoint Folder connector in Power Query.
Once you select the SP folder with all your files in, you can choose to 'Combine & Transform Data' to append all those files together into one:
I already use this connector and I have all data in a single table.
Sorry, my mistake, I had not specified it.
Once you have them all in a single table, you can then use either index + self-merge or something more technical to get the previous/next value on each row.
I need help on this part! 🙂
Thanks
Ale
Ah, cool. I should have looked more carefully at your screenshot.
-1- Sort your data by [Data e ora] ascending.
-2- Add an index column starting from zero and call it [Index0]: Add Column tab > Index Column (dropdown) > 'From 0'
-3- Add an index column starting from one and call it [Index1].
-4- Go to the Home tab > Merge Queries. Select the same query as the second table to merge (you're going to merge the table to itself).
-5- Left Outer merge on Table1[Index0] = Table2[Index1]
-6- Expand [Data e ora] and [Stampa totale] from the nested merge column.
You now have the previous report date and the previous report meter read on each weekly row, so you can now get the meter change value and the number of days over which this change occurred.
There's a few other tidying bits you'll want to do with column names (Start Date/End Date, Start Read/End Read etc.) but the basic principle of what you need to do is here.
Pete
