Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gregor4711
Regular Visitor

Add same set of tables but with valid timestamp field

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

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

  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
    45.PNG

Regards,

View solution in original post

2 REPLIES 2
v-sihou-msft
Employee
Employee

  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
    45.PNG

Regards,

thanks Simon,

work perfect:)

 

BR

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.