Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingDear 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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportinglet
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
You can see this article for creating a custom connector. You need to be more of a programmer though to really do this.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingDear 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
@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingDear Sir,
As per your request I attached a screenshot of mine
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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingDear 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |