Power Query - Transforming Messy Data

[BIAJosh]

Hi. This is my first post. I have MANY spreadsheets that have been formatted for print and I am working to combine and automate these files for easy retrieval and EDA. The files are formatted like this:

I have a process to get to the data in the format that I want, but it is clunky and cumbersome. In short, I clean the data to a point where I have two new queries (one for each "Cost Center" Column) and then I append them as a new query to achieve the table below... Is there a better/faster way to get here??? Thanks!

 

[C Moore]

Are you touching each file twice? One to create each query? Instead of two queries making it rotate through files twice, append within the transform file the first time.

 

[BIAJosh]

I hope this answers your question. I have a working file that is the primary transformation and then I duplicated it to get to two tables that I appended.

 

[C Moore]

File List is the directory and filters for files I want transformed. Summary is all the organized data from the different sheets through the transformation.

For the transformation, I pick out the section that is header information and transform as necessary. Then go back and grab line items from a different section so that when I'm done I hae all the info for each file pulled one time instead of duplicating and making it touch each file twice.