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.
Hi,
A computer create a new .alh file each day. I need all these files in power BI. Can we merge all these files to get only one table at the end? I can make a query for each table one at a time but I have arround 300 files. I can append query to merge it.
There is another way to do this?
Ideally all the new files (of coming days) will be merge with the others automatically.
Thanks for your help.
Solved! Go to Solution.
Hi,
Should be easy doable with the PowerQuery UI then. This assumes all ALH files share the same structure.
1. Open the Query Editor
2. Home > New Source > Folder, and press Connect
3. Browse for the folder path and press OK
4. Press Combine & Edit
5. PowerBI will try to determine the file origin and delimiter for you
6. In the Combine files dialog adjust them as needed, then press OK
You should get a table 'Text Files' with all files combined in a single table. The first column 'Source.Name' contains the file names. If the folder contains also other files that shall not be processed, select the second step 'Filtered Hidden File'. Now you see a list of all files in the folder and you can add additional filter criteria to ensure you process only the files you need (e.g. Extension = ".alh"). Make sure all file filtering steps are inserted before the 'Invoke Custom Function' step though.
You will also notice a 'Sample Query' section with a mix of functions, parameters, and queries in the Queries pane on the left.
The query 'Transform Sample File from Text Files' allows you to add additional transformation steps to apply to every file being imported. Here you can remove top/bottom rows, promote headers, change types, remove errors, or even add calculated columns.
Michael
Hi,
I think this describes the solution...
https://docs.microsoft.com/en-us/power-bi/desktop-combine-binaries
Best regards
Marcus
Hi,
What are ALH files and where do they reside?
Just plain text files, so we can start with the Get Data from Text/CSV connector?
Are they stored on a local PC drive, Sharepoint folders, OneDrive?
I do something similar to append multiple XLS files in a Sharepoint folder to one query.
It may be adjusted for your case.
Michael
Hi,
Yes I can get them with the get data from Text/CSV files. They are in a shared folder on a local PC drive.
Thanks
Hi,
Should be easy doable with the PowerQuery UI then. This assumes all ALH files share the same structure.
1. Open the Query Editor
2. Home > New Source > Folder, and press Connect
3. Browse for the folder path and press OK
4. Press Combine & Edit
5. PowerBI will try to determine the file origin and delimiter for you
6. In the Combine files dialog adjust them as needed, then press OK
You should get a table 'Text Files' with all files combined in a single table. The first column 'Source.Name' contains the file names. If the folder contains also other files that shall not be processed, select the second step 'Filtered Hidden File'. Now you see a list of all files in the folder and you can add additional filter criteria to ensure you process only the files you need (e.g. Extension = ".alh"). Make sure all file filtering steps are inserted before the 'Invoke Custom Function' step though.
You will also notice a 'Sample Query' section with a mix of functions, parameters, and queries in the Queries pane on the left.
The query 'Transform Sample File from Text Files' allows you to add additional transformation steps to apply to every file being imported. Here you can remove top/bottom rows, promote headers, change types, remove errors, or even add calculated columns.
Michael
Thanks to all, it works perfectly but I have to change the file extension for .txt.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |