Lookup data required is not available in an existing data source

[stansabel]

There are times when the data available to Power BI in the database is not complete and cannot be augmented by joining another data source.  I can create an Excel workbook, download the key elements I need from an active database, add columns and then import that file into Power BI.  Is there a way to accomplish this without having a separate process to insure the Excel workbook contains all of the key elements downloaded from the database and the corresponding lookup data manually entered?

I see where we can query columns and then export the data to a CSV file which could then be edited and loaded back into Power BI. 

[v-qiuyu-msft]

Hi @stansabel,

Based on my understanding, tables loaded into Power BI Desktop don't contain common fields, so that you are not able to join those tables, right?

In your scenario, you can export visual's data to CSV, then modify those CSV files with common fields added, then import data from CSV files into Power BI desktop. Each table can be integrated. See: Export data from Power BI visualizations.

You can also use Copy Table property to copy all tables data, then paste in a Excel worksheet. And modify Excel then import data into Power BI again. Please refer to screenshot below:

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[stansabel]

Thank you Qiuyun.

The challenge is that the source data returned from the database query is constantly changing.  If I'm following your suggestion, I would have to recreate the local CSV/Excel file every time the source data changes.  

Here's another way of looking at the issue.  I use Power BI to query a list of all ETL imports that failed up until the time I run the query.  I'd like to update the result set with a contact name reflecting the person assigned to the next step.  When I refresh the Power BI query later in the day, there are new processes that failed.  Today, it appears I will have to export the data again and run a separate process to keep it in sync with what was previously updated (in the CSV/Excel exported file).

Is there anyway to export the data and have it update an existing CSV/Excel document?

[v-qiuyu-msft]

Hi @stansabel,

As far as I know, each time you export data from Power BI, you need to replace the existing Excel or CSV file, then modify the table manually to add common fields for Lookup.

In your scenario, would you please try to create a new source table which contains all necessary data, then retrieve data from this table directly.

Best Regardas,
Qiuyun Yu