Forum Discussion
Data model design help - Adding rows to imported excel table and making new table persistent
Hi,
I'm looking for advice on the direction to pursue for the following task (simplifed for ease of explanation).
I'm creating a table (let's call it CurrentMonthSummary) with DAX that groups and summarises data from a large main table (let's call it CurrentMonthDetails). This report will run every month as is implied. For Month 1 this is trivial. For Months 2+, the report will need to show differences between the current month and the previous month. So I either end up with a summary table called CurrentMonthSummary and table called PreviousMonthSummary, or just stick them in one table and use a column to distinguish between.
The challenge I'm having is figuring out how to manage the transition of data from current month to the previous month, and make it persistent. I'm not sure whether to automagically turn the CurrentMonthSummary's data into the PreviousMonthSummary data using PowerBI (and refresh CurrentMonthSummary from the CurrentMonthDetail table), or to add a manual step to the monthly process that requires exporting data from last month's CurrentMonthSummary into an Excel sheet and using that as an data import for PreviousMonthSummary.
Doing it using PowerBI feels wrong as its a data visualisation tool, and not meant for creating offline data stores but maybe I'm missing some good functionality it has to accomplish this. To add spice to the pot, this will probably turn into a requirement to store the summary data for all previous months for trending purposes...
Thanks in advance for your thoughts and opinions.
Hi, if you want to keep history of data, you need to decide on how to store it. I wouldn't go down the route of outputting copies of data from Power BI. I would personally set up your file store to keep files for each month and then append them within Power BI in the query editor. It will be less work in the long run for you, rather than exporting data out, and re-importing. Just change the file name to include a date / month of export to ensure that the data exports aren't overwritten, and as mentioned use the sharepoint / file connector to append data in power bi - see this video for further assistance. https://www.youtube.com/watch?v=RAmSdxt863s - just make sure that the files ideally keep the same format month on month.
7 Replies
- Hi,@injeejay if you are working with a data warehouse then the table you store the data in would be a snapshot fact table, meaning it would store successive monthly snapshots in the same table, appended one after the other.
If you are working with extracts and excel/csv files in a filestore or share point/OneDrive you can employ a similar technique to pull in multiple files and append these files together. The sharepoint or file connector will allow you to bring in multiple similar files and merge them together in power bi via the PowerQuery query editor (Get Data). You can choose the range of files to bring in based on the file name or based on a date column in the source files (this would be slower as power query would have to read all files before filtering their content).
Once you have all the data in a table then you can use measures with time intelligence to compare figures for current month to previous months.
Hope this helpsHi Danno,
Allow me to clarify a bit further my issue. I've already imported and merged a range of tables (from Excel spreadsheets on a Sharepoint) to create a master table in PowerBI of all records for a particular month - let's say June. I'm then creating a new table in PowerBI, using a range of DAX expressions, that summarises values from the master table. So this will be my June summary table and I can make nice KPI visuals for reporting.
However, come July, I will refresh all the data in my model with new Excel spreadhseets and create a new master table, and this table will have no memory of data from June. I will also create the July summary table using the same DAX expressions as before.
My issue is I don't know how to go about comparing my July summary table to my June summary table. I know this sounds stupid, but where will my June summary exist? Before refreshing my data in July, can I get PowerBi to export it to an excel spreadsheet so that it persists, and I can then import it as "previous months" data?
If I can't do this, can I just keep adding each months summary data to the summary table, and add a column to say which month it is?
Ideally, I'd like to just have one table like below (heaviliy simplified but gives the idea), that each month adds in the summary data from the new month to all the previous months. At the moment, my plan is to manually export this table from PowerBi each month after it is updated, and import it for the next month as "previous month data" and add the new data to it - so if we looked at this table after the running the report in July, the July column would be populated.
