Excel Power Query, query.

Associate
Joined
2 Oct 2004
Posts
1,048
Hi all,

Sorry in advance, not the best at explaining these things but here goes.

I’ve very recently discovered ‘Get & Transform’ or Power Query in Excel, and have found some great use for it, there is one thing I’d like to achieve with it however, but I’ve no idea if it’s possible.

I have a set of auto generated txt log files in a folder which contain rows of machine logs, from this I’ve been able to determine material usage and wastage. To do this, I have to manually sort the data, I do this by appending all the log files in the folder, copy to a spreadsheet so all the data is in column A, then use a macro which pulls across 40 headers from another tab and splits the data perfectly according to the header, I then copy/paste this formatted data into a spreadsheet where I have various COUNTIFS, SUMIFS etc to count the usage and wastage. I didn’t create the spreadsheet or the macro that enables me to format the data

What I would like to do using power query is to automate this, so when a new log file is created, it automatically splits each line into the 40 column headers and adds to my table, thus eliminating me having to do anything.

I’ve had a go at splitting the column with the options available but it can’t be done by doing it by comma, spaces, or certain characters etc as there isn’t a pattern to it if that makes sense.

Just wondering if there is a way to essentially copy the code from the macro and have it applied to the data when it’s updated.

Thanks in advance.
 
Back
Top Bottom