cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kana
Helper I
Helper I

How to union dbase files into 1 query and also a custom column

Dear All Master

With this I want the UNION database to be 1 query in the power query. I already used union in the connection string but I can't custom the columns for each dbf table. What I want as follows:

  1. in the BOJ folder with the database name IFG.DBF I want to custom the column with the name DEPT and the contents of the column is "BOJ", in the M18 folder with the database name IFG.DBF I want to custom the column with the name DEPT and the contents of the column are "M18", in the MD2 folder with the database name IFG.DBF I want to custom the column with the name DEPT and the contents of the column are "MD2".
  2. Can I union to the first point with a different DBF database (STF.DBF in each folder)
  3. I want a function of the M code language to speed up database loading because database records are in the hundreds of thousands.
  4. I hereby attach the sample file link: https://drive.google.com/open?id=1wyCZuaAUBeXDVksp7Wt1St1ITEpciIk_

 

let
    Source = 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#(lf)"")")
in
    Source

 

16 REPLIES 16
gustavoleo
Helper II
Helper II

The only chance that you have to work with DBF files on Power BI is

using Devart OCBC Driver here -->> https://go.helpbi.com/dbf and Power BI Dataflows

First, create a Dataflows and then on another dataflow( if you have premium per user) make all the data mashup on a reference Entities /Table.
If you don't have a premium, make your transformations on Desktop from your DBF converted to a Power BI Dataflows

edhans
Super User III
Super User III

I cannot open a RAR file, but if what is in there is a bunch of dBase files it won't help much as don't have dBase or any ODBC setups for it in 64 bit.

 

That said, you need to create a query for each table, then use the Append (Table.Combine) operation.

 

DBF files don't support query folding, so your SQL statement isn't being sent to a database engine, and Power BI doesn't know anything about DBF files - it is relying on the ODBC driver.

 

You might check out this article. It runs it through Access. Access does support folding as Power BI will use the JET engine running on a PC that has Access installed, though that is for ACCDB files. Not sure if this trick will work.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Dear Sir,

I don't want to make a query for each table, then use the Add operation (Table.Combine) and don't want to also via ACCDB. I want to direct 1 query from each table. I can use union only, the problem is I can't custom the columns for each dbf table or if there are other tricks from several dbf tables directly to the excel power query.

 

Thanks

 

Kana

edhans
Super User III
Super User III

Ok. I wish the best for you in figuring this out. Power BI just wasn't designed much with dBase tables in mind. There are 114 data sources here, and dBase isn't one of them. Might consider having a custom connector written. The documentation starts here.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

let
    Source = 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#(lf)"")")
in
    Source

Dear Sir,

Ok please help me to custom the connector.

The code that I use in the connection string can be just that I don't add custom columns in each dbf table.

 

Thanks

 

Kana

 

edhans
Super User III
Super User III

You can see this article for creating a custom connector. You need to be more of a programmer though to really do this.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Dear Sir,

 

Please guide me if using a custom connector and how to if via ACCDB

 

Thanks

 

Kana

Dear sir,

 

I have a solution, Can you help if the custom column that I want is based on the folder path. So please help me in adding code in the advanced editor in the power query.

 

let
    Source = Odbc.Query("dsn=DBASE FILES", "Select * From C:\Test\mytest\m18\IFG.DBF Union ALL Select * From C:\Test\mytest\boj\IFG.DBF Union All Select * From C:\Test\mytest\md2\IFG.DBF"")")
in
    Source

 

Thanks

 

Kana

edhans
Super User III
Super User III

@kana I'm not clear on your question. Can you post a screenshot of what the above code shows on your computer, and what your expected output is? I cannot test the code because I don't have dBase files nor the dBase ODBC driver. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Dear Sir,

As per your request I attached a screenshot of mine

SCREENSHOT-1.jpg

From the code that I tried successfully, is it possible if I custom a column based on the path of the folder with the Code M language.

Thanks

 

Kana

 

 

 

edhans
Super User III
Super User III

Sorry, I am not clear exactly what "From the code that I tried successfully, is it possible if I custom a column based on the path of the folder with the Code M language." means.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Dear Sir,

My point is successfully using the script in sql statement to merge the dbase file into 1 query, the problem is I want a custom column. So I ask you whether you can custom column based on the folder path with the M language function code language?

 

Rgrds

 

Kana

Dear Sir,

 

 

Please give me solution.

 

Thanks

kana

It is difficult to understand what you are saying.

 

What you can do is add a custom column to a table, each one with a different connection string based on each row. Then, add another custom column with the connection to the db. E.g.

Table.AddColumn(<<Your Table with Databases>>, "Tables", each Odbc.Query(<<A formula that determins the database to connect to>>))

Finally, you just need to expand that column to do the union. 

Dear Sir,

 

I know what you mean by using a formula to create custom columns by analyzing each row. The problem is that it cannot identify every line because there are no markers. Now I am asking if the function of the M language code is to add a custom column based on the folder path.

 

 

Thanks

 

Kana

 

 

@kana  You can start with Folder.Files("Path to BOJ folder")

From there filter the files for the ones you want, then add a custom column as mentioned.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors