Excel Power Pivot provides a powerful tool for efficiently transforming data. With just a few clicks, I created a query to combine all tabs into a single dataset. I opened a new Excel sheet and selected the file provided by my customer as the source data.

I’m now presented with the navigator where I need to select the main folder and click on “Transform Data.”

Now I have my Query where I can remove the columns I don’t need. First, I can select those columns and use the “Remove Columns” function in the toolbar.

Next, I must expand the data so that all my accounting periods and amounts are populated in the dataset and ready for the next transformation. 

I need to select the “Data” column and click on the icon to the right, making sure all my columns are selected before clicking “OK.” 

I can then promote my first row as headers.

Now that my dataset has been defined, it’s time to unpivot the columns to get a new data set that I can use to create General Journal entries with. For this step, I need to select all the columns I want to unpivot then select the “unpivot columns” function in the “Transform” menu.

My columns have now been unpivoted!

The last step is to simply load the data into Excel! All I need to do is to select the “Close & Load” function in the “Home” menu.

So, now I have a dataset that can be used easily as the source to create the General Journal entries and post to the General Ledger!

I want to point out that this dataset had just over 100,000 entries and it only took minutes for me to get from the source file to this dataset that I can now use.