Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I've had a search but can't seem to find a direct answer to this...
For a rental business, I have a source table in excel which contains a row for each product. Many other fields in the table are blank for each product until such time as the product becomes reserved (e.g. weekly rental amount(£), start date, end date, length of rental term, etc, etc). When a cutomer rents the product these other fields become populated with the relevant data.
There is no 'reservation date' field, so there is currenly no timestamp as to the date when each product is reserved (I'm hoping to change this, but let's assume I can't for now).
I get a daily feed of this data - the whole table.
What i'd like to do is load the table into PBI, and effectively take a snapshot of that day's data. Insert a timestamp against newly 'reserved' products. Then, on the next day, I'd want to refresh the same table, but not overwrite the timestamps and data from the previous file... just add any new data for products which have been reserved between the two data loads - i.e. i want to only update the PowerBI report with the delta of the data.
Is this possible? If not, is there a good workaround?
Many thanks.
Solved! Go to Solution.
Hi @Nickodemus
Actually it is a problem in Power BI, as it don't stores previously shaped data somewhere, where it can take from later.
But you can do it in the Excel as "self-referenced table".
You can do it this way (in Excel):
1. Create a Query1 to your source (daily updated) Excel table. Add timestamp
2. Load the result of this Query1 to a sheet (once).
3. Create a new Query2, pulling data from the resulting table (on the sheet) from step 2.
4. Append the result from Query1 to the result of step 3
(5. Remove duplicated rows if needed)
For doing the same in PBI, you need to find a way to store previous results from PBI. There are some ways described by @ImkeF in her blog.
Hi @Nickodemus
Actually it is a problem in Power BI, as it don't stores previously shaped data somewhere, where it can take from later.
But you can do it in the Excel as "self-referenced table".
You can do it this way (in Excel):
1. Create a Query1 to your source (daily updated) Excel table. Add timestamp
2. Load the result of this Query1 to a sheet (once).
3. Create a new Query2, pulling data from the resulting table (on the sheet) from step 2.
4. Append the result from Query1 to the result of step 3
(5. Remove duplicated rows if needed)
For doing the same in PBI, you need to find a way to store previous results from PBI. There are some ways described by @ImkeF in her blog.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |