• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to combine multiple data sources and tables in one query in a power query

R

roykana

Member
Dear All Master,

please help me how to combine multiple data sources and multiple data tables in one query in a power query. The database that I use is the dbf database.
I can only make one data source with one dbf database table in one query in a power query.
Please help me to solve this problem. Please provide solutions with step by step


Thanks

Roykana
 
So it's dbase file?

May be use Union/Join etc in native query?

But without some sample we can't really help other than to say... "Google it" or "Upload sample".
 
Chihiro said:
So it's dbase file?

May be use Union/Join etc in native query?

But without some sample we can't really help other than to say... "Google it" or "Upload sample".
Click to expand...
yes really dbase or dbf database .

You mean I give the dbf database sample
 
When bringing in data from dBase. What connector are you using?
I assume you are using OLEDB to dBase folder.

Write query that Union/Join tables (or query) as required using SQL statement.
 
roykana said:
if I give the advanced editor code to each query whether it can already be sampled
Click to expand...

I use ODBC in Excel and I attach the advanced code editor from 6 queries. So I want to be a single query in the power query and I want the data to load quickly with m language
 

Attachments

  • Code Advanced editor from 6 query.docx
    14.9 KB · Views: 13
roykana said:
I use ODBC in Excel and I attach the advanced code editor from 6 queries. So I want to be a single query in the power query and I want the data to load quickly with m language
Click to expand...
previously I made 6 queries on the power query from 6 odbc data sources and 6 tables. Can it directly be 1 query in the power query in M language without combining 6 previous queries. Additional information I use excel 2010 and power query (PowerQuery_2.62.5222.761 (32-bit) [en-us])
 
Instead of ODBC use OLEDB.

Then you can use SQL query, assuming table structures are same between .dbf files.

Ex:
Code: 
let
    Source = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\YourdbfFolder\;extended properties=dBASE IV", [Query="select * from [myFile.dbf] union select * from [2nd.dbf]"])
in
    Source
 
Chihiro said:
Instead of ODBC use OLEDB.

Then you can use SQL query, assuming table structures are same between .dbf files.

Ex:
Code: 
let
    Source = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\YourdbfFolder\;extended properties=dBASE IV", [Query="select * from [myFile.dbf] union select * from [2nd.dbf]"])
in
    Source
Click to expand...
Dear sir
thank you for your answer. How to connect to dbf database via oledb in excel 2010 and power query. Because in Excel 2010 I don't have the OLEDB option.

Thanks
Roykana
 
Just checked. You should be able to do something similar in ODBC. Though I don't have dBase so you'll have to do some testing yourself.

Query goes in below pane in From ODBC dialog.
65931
 
roykana said:
thank you for the information i will try the guide from you
Click to expand...
Dear sir
The code you provided has been tried successfully but I have the following problem:
1. I want to combine in one query with three drive folders that have 2 dbf files to make one query
2. If it has successfully become 1 query then I want to custom the column of each dbf file with the name "DEPT" and fill in "BOJ", "M18" and "MD2" (for the BOJ folder then fill in the BOJ, for the m18 folder then fill in the contents M18 and the MD2 folder in the contents of MD2 is only an example because the original was indeed three folders with each folder drive and folder names are not like that). After that, only for IFG.DBF database from 3 folders, the custom column with the name "DSC" is filled with "STOCK"
3. In the excel file named CONNECT with a sheet named "SAMPLE VLOOKUP WITH FORMULA" in column c I marked yellow, so if I use merge from the power query then I don't want to unpivot the master item no because there are originally 30000 lines so that if it is unpivoted the line becomes 90000 then this makes more data. So I want the M code language formula or any other solution to be super fast vlookup with the same goal or result as the formula in the "SAMPLE VLOOKUP WITH FORMULA" sheet so that it doesn't make calculating processors that make it very slow
4. Actually, the dbf database record is over 200000 lines so I want to use Table.Buffer and
List.Buffer to speed up the process. I hereby attach sample dbf database files from 3 folders.

Thanks

Roykana
 

Attachments

  • SAMPLE.rar
    99.1 KB · Views: 1
roykana said:
Dear sir
The code you provided has been tried successfully but I have the following problem:
1. I want to combine in one query with three drive folders that have 2 dbf files to make one query
2. If it has successfully become 1 query then I want to custom the column of each dbf file with the name "DEPT" and fill in "BOJ", "M18" and "MD2" (for the BOJ folder then fill in the BOJ, for the m18 folder then fill in the contents M18 and the MD2 folder in the contents of MD2 is only an example because the original was indeed three folders with each folder drive and folder names are not like that). After that, only for IFG.DBF database from 3 folders, the custom column with the name "DSC" is filled with "STOCK"
3. In the excel file named CONNECT with a sheet named "SAMPLE VLOOKUP WITH FORMULA" in column c I marked yellow, so if I use merge from the power query then I don't want to unpivot the master item no because there are originally 30000 lines so that if it is unpivoted the line becomes 90000 then this makes more data. So I want the M code language formula or any other solution to be super fast vlookup with the same goal or result as the formula in the "SAMPLE VLOOKUP WITH FORMULA" sheet so that it doesn't make calculating processors that make it very slow
4. Actually, the dbf database record is over 200000 lines so I want to use Table.Buffer and
List.Buffer to speed up the process. I hereby attach sample dbf database files from 3 folders.

Thanks

Roykana
Click to expand...
Chihiro said:
Just checked. You should be able to do something similar in ODBC. Though I don't have dBase so you'll have to do some testing yourself.

Query goes in below pane in From ODBC dialog.
View attachment 65931
Click to expand...
Dear Mr. Chihiro

What is the next process for the single query?


Thanks

Roykana
 
1. Can't help you there. I'd imagine there is way to do it using IN statement. But I don't have dBase to test with.

2. Not sure what you mean. But if you need some custom column, you could add it at query stage or at transformation stage in M.

3. Just perform join in M query.

4. Table buffer won't help you here, since you are trying to combine all 6 into one query (using SQL query). No need to buffer.

FYI - I don't use rar archive, so I haven't looked at your latest upload.
 
Chihiro said:
1. Can't help you there. I'd imagine there is way to do it using IN statement. But I don't have dBase to test with.

2. Not sure what you mean. But if you need some custom column, you could add it at query stage or at transformation stage in M.

3. Just perform join in M query.

4. Table buffer won't help you here, since you are trying to combine all 6 into one query (using SQL query). No need to buffer.

FYI - I don't use rar archive, so I haven't looked at your latest upload.
Click to expand...
Dear sir
You don't have a zip, I want to send the dbf file to you. How do I send it to you?
Thanks

Roykana
 
Ok, it was rather simple. Use dBase ODBC connection string for connecting to remote drive.
i.e. Don't put anything other than data source name.

Then use full path to each file when querying.
Ex:
Code: 
= Odbc.Query("dsn=dBASE Files", "Select * From C:\Test\mytest\m18\IFG.DBF Union Select * From C:\Test\mytest\boj\IFG.DBF Union Select * From C:\Test\mytest\md2\IFG.DBF")

Use this pattern to Union all 6 files.
 
Chihiro said:
Ok, it was rather simple. Use dBase ODBC connection string for connecting to remote drive.
i.e. Don't put anything other than data source name.

Then use full path to each file when querying.
Ex:
Code: 
= Odbc.Query("dsn=dBASE Files", "Select * From C:\Test\mytest\m18\IFG.DBF Union Select * From C:\Test\mytest\boj\IFG.DBF Union Select * From C:\Test\mytest\md2\IFG.DBF")

Use this pattern to Union all 6 files.
Click to expand...

can you provide an advanced editor screenshot in the power query?
 
The process is done before you get to query editor.

see the screenshot in post #10. Enter Select * form... part only in sql statement pane.
 
Elaborate "How do I want to custom the columns for each dbf table. ". If you mean you need custom column for each DBF file that's being unioned. That's not really Excel question, as you'll need to write SQL Statement for DBF.

Any Union can only be done for table with same structure. If it isn't, you can't.

Depending on data, you can perform join. But that really depends on data structure. But again, that's not really an Excel quesion anymore ;)
 
You must log in or register to reply here.
Back
Top