Forum Discussion

kana's avatar
kana
Icon for Helper I rankHelper I
6 years ago

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

 

Need Help

16 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.

    • kana's avatar
      kana
      Icon for Helper I rankHelper I
      6 years ago

      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

  • gustavoleo's avatar
    gustavoleo
    Icon for Advocate II rankAdvocate II
    5 years ago

    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