Forum Discussion
Populating empty tables with data
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.
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.
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.
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.Anonymous
You are right, if you create a table using functions and formulas, it will not appear in Power Query mode.
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.