power query

[FRG]

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.

[mwegener]

Hi,

I think this describes the solution...

https://docs.microsoft.com/en-us/power-bi/desktop-combine-binaries

Best regards

Marcus

[Michael-PBI]

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

[FRG]

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

[Michael-PBI · Accepted 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.

Add additional filter criteria for file names here

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.

Add additional transformation steps to apply to every file here

Michael

[FRG]

Thanks to all, it works perfectly but I have to change the file extension for .txt.