Excel Power Query, query.

[BYTEr]

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.

 

[h4rm0ny]

I'm afraid I don't quite follow what you're doing exactly. Sample data would be useful. For example in such situations, I often just search and replace the seperators in the file with tabs and then paste it directly into Excel to get the columns I need. Sample data might help us advise the best approach. However, to answer the question about copying the code from a macro, you can open the Macro under Developer tools ribbon. If this isn't displaying by default (but it probably is because you're talking about using Macros already), just go to File -> Customise Ribbon / Menu (I forget, exactly). That will show you the contents of your Macro as code. You can copy and paste it anywhere.

After that, what you're talking about is automatically calling the code when a cell changes. You do that, with intersects. Here's a sample piece of code. You'd put it in the Module file for your worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("D2")) Is Nothing Then Exit Sub
        Application.EnableEvents = False 'to prevent endless loop
        On Error Goto Finalize 'to re-enable the events     
        MsgBox "You changed THE CELL!"
    End If
Finalize:       
    Application.EnableEvents = True
End Sub

Again, you find the module file in the same way you do with your macro code - switch to the Developer ribbon and select "Visual Basic".

I hope that gets you started.