PowerQuery: Combine TXT files based on multiple deliminators

Hey all!

I am looking to combine these files "Drive to folder/files" from a folder level. The issue is that when combining i can only select 1 deliminator, either comma or semi-colon. If it was just these 2 files I would change the deliminator in one of the files, there are numerous files and i would prefer not to edit them (even if a script can do it all for me!).

DAX:

Please Login or Register  to view this content.

Any thoughts?

Thanks in advance!

Could you attach relevant files to the thread using our attachment facility, please? Include the Excel workbook that you are using to import the files.

Certainly.

PQ Help - July23.xlsx , DailyOpenAM.TXT , DailyOpenAM.TXT

I haven't found a way from within PowerQuery, although that doesn't mean there isn't one.

Code might be your best option for now.

You could change your Transform function to something like this:

Please Login or Register  to view this content.

so it will attempt to parse using a comma and then if there is no heading in column 2 (because all the data stayed in one column) it will attempt to parse using the semicolon instead. Not sure it will be foolproof - e.g. if one or more of your column headers included a comma - but it seems to work with your samples.