Power Query - Transforming Messy Data

BIAJosh

New Member
Joined
Apr 27, 2022
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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:
1651070402325.png


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!

1651070698367.png
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
626
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 27, 2022
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jan 17, 2014
Messages
626
Office Version
  1. 365
Platform
  1. Windows
1651075797446.png

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.
1651075898180.png

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.
 

Forum statistics

Threads
1,176,105
Messages
5,901,407
Members
434,891
Latest member
mahmoud Alhamaky

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top