Forum Discussion
Helper IHow to union dbase files into 1 query and also a custom column
Community ChampionOk. 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.
Helper Ilet
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
SourceDear 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
You can see this article for creating a custom connector. You need to be more of a programmer though to really do this.
- kana
Dear Sir,
Please guide me if using a custom connector and how to if via ACCDB
Thanks
Kana
- 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 SourceThanks
Kana
artemusMicrosoft Employee
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.
- kana
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