Combine 2 tables from different sources

Question

Hi thereIm quite new to Power BI so please bare with me 🙂I have an Excel spreadsheet which is a static spreadsheet that I havce used as a datasource (it contains invoices).Then Im using Jet Analytics as an other datasource which is integrated into power bi using the SQL Server analysis server database.the Excel files contains al the data from 2019 - 2023, and the SQL Server/Jet Analytics contains all the invoice data from 2024 and as long as we use Business central.It's not an option to get the old invoice data into BC, and Im not sure if it's an option to combinde the 2 datasources to 1 table in Jet analytics.But for some reason I can't combine these 2 datasources in Power Bi and make 1 table ..are there any solution to my problem in Power Bi ?Please feel free to ask any questions if it doesn't make sense what Im trying to do ..

truttafisker · Answer

I actually found this post ..&nbsp;https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Union-multiple-tables-with-uneven-columns-and-then-concatenatex/m-p/4024059#M158830and it seems to being able to do the trick ..&nbsp;My only concern is .. If i make my own table like this, will the "new" table's data also being updated, when the data from the SQL server is being updated ??

hotchilli · Answer

"But for some reason I can't combine these 2 datasources in Power Bi and make 1 table" - What method are you using? How far have you got?

truttafisker · Answer

I've tried the "merge" function but none of the tables (other than my excel files) shows up in the tables to choose from,.&nbsp;

anonymous · Answer

Hi,&nbsp;HotChilli&nbsp;
Thanks for your concern about this issue.
&nbsp;
Hi,&nbsp;truttafisker&nbsp;
I am glad to help you.&nbsp;&nbsp;
&nbsp;
According to your description, you want&nbsp;to know will the "new" table's data also being updated, when the data from the SQL server is being updated?
&nbsp;
If I understand you correctly, then you can refer to my solution.&nbsp;
&nbsp;
First, you need to confirm that your 2024 data is loaded into Power BI Desktop. If you have successfully loaded it in, you can theoretically find this corresponding Table in the Merge page.
Of course, as you said you found a solution in the community using DAX, which is also possible.
Regarding the question of whether the new table will be refreshed when the data in SQL Server is refreshed, you have to make sure that the data in SQL Server exists on-premises first.
If it is on-premises, you have to install and configure the corresponding on-premises data gateway to ensure that the data can be refreshed normally.
&nbsp;
This are some official documentation, hope help you：&nbsp;
DirectQuery in Power BI - Power BI | Microsoft Learn&nbsp;
Data refresh in Power BI - Power BI | Microsoft Learn&nbsp;
Automatic page refresh in Power BI Desktop - Power BI | Microsoft Learn&nbsp;
Manage a SQL Server data source - Power BI | Microsoft Learn
&nbsp;
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Combine 2 tables from different sources

4 Replies

Related content

Medallion Architecture - best practices to combine data sources

Combine different sources with different column names

Tables combine

Combining Data from Different Semantic Models

Append Queries 3 different data sources