Append multiple DBF files in PowerQuery

Create a function query which does the manipulation on your source table, based on Path / File parameters:

fnGetDBF

(MyPath, MyFile) =>
let
    Source = Odbc.DataSource("dsn=dBASE Files", [HierarchicalNavigation=true]),
    Database = Source{[Name=MyPath,Kind="Database"]}[Data],
    Table = Database{[Name=MyFile,Kind="Table"]}[Data]
//  Your transformations here

in
    Table

Use this function to combine all the dbf tables in a folder:

CombineDBFTables

let
    Source = Folder.Files("C:\YourPath\DBF"),
    #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".dbf"),
    #"Added MyPath" = Table.AddColumn(#"Filtered Rows", "MyPath", each Text.TrimEnd([Folder Path],"\")),
    #"Added MyFile" = Table.AddColumn(#"Added MyPath", "MyFile", each Text.Replace([Name],[Extension],"")),
    #"Get Tables" = Table.AddColumn(#"Added MyFile", "fnGetDBF", each fnGetDBF([MyPath], [MyFile])),
    #"Combine Tables" = Table.Combine(#"Get Tables"[fnGetDBF])
in
    #"Combine Tables"

Post your single file query which works.

It looks like this is the only change needed:

fnGetDBF

(MyPath, MyFile) =>
let
    Source = Odbc.DataSource("dsn=dbf_pliki", [HierarchicalNavigation=true]),
    Database = Source{[Name=MyPath,Kind="Database"]}[Data],
    Table = Database{[Name=MyFile,Kind="Table"]}[Data]
//  Your transformations here

in
    Table

And changing the path to yours:

CombineDBFTables

let
    Source = Folder.Files("C:\USERS\USER\DESKTOP\DBF_FILES"),
    #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".dbf"),
    #"Added MyPath" = Table.AddColumn(#"Filtered Rows", "MyPath", each Text.TrimEnd([Folder Path],"\")),
    #"Added MyFile" = Table.AddColumn(#"Added MyPath", "MyFile", each Text.Replace([Name],[Extension],"")),
    #"Get Tables" = Table.AddColumn(#"Added MyFile", "fnGetDBF", each fnGetDBF([MyPath], [MyFile])),
    #"Combine Tables" = Table.Combine(#"Get Tables"[fnGetDBF])
in
    #"Combine Tables"

Make sure you name the fist query "fnGetDBF".

Better?

Originally Posted by matvizzari

Good morning Olly, I am very new to this power query and I want to implement, raise multiple tables in DBF from various databases, all databases have the same tables, since each database belongs to a branch different.
I'm reading this but I can't understand how I should put it together. Can I pass you the base query that I have of a table and would you help me adapt it to be able to raise all of them more automatically?

From already thank you very much

let
Source = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\matiasvizzari\Documents\Lince\DPILAR\DBF\;extended properties=dBASE IV",
[Query="select * from [ped.dbf]"])
in
Source

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Originally Posted by wrybel

It works but now I'm getting Formula.Firewall Error

Post your exact queries, please. I don't get that error.

No, please post your QUERIES. The queries I posted don't result in that error, so I am guessing you've added / changed something in the queries.

Have you changed the Source line, in query "CombineDBFTables" ?

Also - check the privacy settings for each data source. You could either ensure the privacy levels are set the same for all sources, or you could ignore the privacy levels in this workbook (fast combine).

Great, glad we got it sorted!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You can also add reputation to those who helped you. Thanks.