Forum Discussion
Calculate weekly usage of a printer
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
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