Populating empty tables with data

[Anonymous]

Hello,

I created a data model with empty tables in the hope of populating them with data once data becomes available. I've been trying to find ways to do that but have not been able to find that. Most of the articles I found talk about creating a data model with tables that already have data in the source.

How can I populate data into my empty tables?

Regards,

Azfer

[darentengmfs]

@Anonymous 

What are you trying to accomplish? You can always append your new data table to the empty tables.

[Anonymous]

I created a cost table with campaign, category, mac, and quantity_imps columns. I then created relationships with the cost table and others. When cost data becomes available, I'm hoping to populate that data in the cost table without creating two queries. 

I am aware of Appending Queries such as this article but this creates two queries. Reason for wanting to keep queries low is for overall maintainability as the data model is going to have upwards of 25 tables. If I do this for every single table, there will be double the number of queries.

Again, my goal is to simply bring in ONLY the data from the source into the target table in Power BI.

Hope that is clear. If not, please let me know.

[darentengmfs]

@Anonymous 

If that's the case, you can change your source of your query under Advance Editor. Change it to whatever is used to pull your data. The rest of the steps will be applied in the same table.

[Anonymous]

Hi @darentengmfs,

Thanks for the tip. I don't see the table I created in Advance Editor. I think it's because I created the empty table using DATATABLE function. Is that true?

I first created a table using the following code:

ap_leads = DATATABLE(

"mac_code",STRING,

"mac_description",STRING,

{

{"A123","Test MAC"}

}

)

Then, I tried to Transform data and in my Queries list, I didn't see "ap_leads".

It seems like there is no way but to have two queries for each table that will be in the data model; the first query being the empty tables, the second query being the actual data source.

[darentengmfs]

@Anonymous 

You are right, if you create a table using functions and formulas, it will not appear in Power Query mode.

[Anonymous]

@darentengmfs 

Should I be concerned with having twice as many queries as needed? I guess what are the areas this will have an impact on, besides having a messy list of queries.

[darentengmfs]

@Anonymous 

I need to get this clear.

You have an empty table with a list of steps. You are then importing a dataset into your model and want the new dataset to have the list of steps, just like the one in the empty table?

[Anonymous]

@darentengmfs 

I'm sorry about the confusion as I might be using different lingo than what is traditionally used. Hopefully my screenshot below does a better job of what I'm trying to accomplish.

I'm getting hung up on double the queries because I'm worried it might have an impact on the performance. If it's a moot point, please let me know. 

I understand creating the empty table with a list of steps completely. I'm not sure I understand when you say "...the new dataset to have the same list of steps" because steps like renaming columns wouldn't need to exist if I connect to the real data source, which will update daily or weekly. There are probably other steps as well that won't be needed like changing data type.

Once again, thank you very much for taking the time to understand the situation.

Regards

[v-jingzhang · Accepted solution]

Hi @Anonymous 

I don’t recommend you to create the empty table with functions in Power BI because it will be difficult to replace the data. Instead, I recommend that you create the empty table outside power BI using the same data source type with your BigQuery table if it is possible. In this way, you can use this empty table (in which there is some sample data) to create the data model and reports. When you want to connect the report to the BigQuery table, you can change the data source easily in Data source settings. The data from the new data source will then be used in your report after applied.

If it is not convenient for you to create an empty table with the same data source type as BigQuery table, you can also create it with another type, e.g. an Excel Workbook. Remember to build the same table structure in the excel file and connect to this excel file in Power BI to create your model. When the real data is ready, you can create a new query connecting to the BigQuery table source, then go to Query Editor->Advanced Editor, copy the Source and paste it in your original empty table query’s Advanced Editor to replace its Source. At last, you can delete the new query created in previous step.

For more details and examples, you can refer to these links:

https://blog.decisivedata.net/blog/change-a-power-bi-data-source-type

https://community.powerbi.com/t5/Desktop/Change-excel-datasource-to-Web-sharepoint-file/td-p/330296

https://community.powerbi.com/t5/Desktop/Change-of-data-source-from-excel-to-sharepoint/td-p/515417

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.