Add same set of tables but with valid timestamp field

[gregor4711]

Hi all,

I have a imported set of tables combined to a data model and query / charting the data which I need.

So far so good, I’m really impressed with Power BI.

To make the solution more productive I plan to import the same set of Tables (which already in data model)  every week and query 7 charting  the changes over the time.

Since the original database (where the data came from) have no issue date, I need a capability to indicate when the record was valid.

This would provide a 2 detention time model for reports like “We improve the numbers of xxx over the time of xx weeks about 40%” etc.

My plan is to have I additional time stamp / Date field with each table,  when the record was imported into Power BI database.

So my 2 questions for this scenario are:

1. What is the best way to attaches new data in the same structure of Tables / data model each week into the database with a additional (not in imported data structure)  valid Date field?
2. Is there a “PBI-system” field which I can query when the data are imported into PBI Database?

Just to let you know, I’m working only with PBI desktop

Cheerio

Gregor

[v-sihou-msft · Accepted solution]

1. What is the best way to attaches new data in the same structure of Tables / data model each week into the database with a additional (not in imported data structure)  valid Date field?
   
   You can add a  last refreshed column in Quert Editor, like:
   

   Last Refreshed  = DateTime.LocalNow() 

   It will change once your refresh your dataset. For more details, please refer to links below:
   last time refresh
   Display Last Refreshed Date in Power BI
2. Is there a “PBI-system” field which I can query when the data are imported into PBI Database?
   
   Once you open a .pbix file, it will generate a temp SSAS workspace. You can connect this database in SSMS. Please see: Connect to Power BI Desktop Model from Excel and SSMS. Then you can query the DMV for this temp SSAS database and see the LAST_DATA_UPDATE time.
   

   SELECT * FROM $System.MDSCHEMA_CUBES

   
   
   

Regards,

[gregor4711]

thanks Simon,

work perfect:)

BR