Combine Multiple Workbooks in Power Query - When File Structures are Different

S

snew

New member
Joined
Nov 3, 2016
Messages
4
Reaction score
0
Points
0
I have some follow up questions related to Ken's Feb. 25, 2015 blog about combining multiple Excel workbooks, which was excellent and very helpful. My goal is to create a consolidation workbook for the 4 attachments that contain the name "Carrington Ridge." These files represent very typical profit & loss statements for four different time periods (2013, 2014, 2015 and twelve months ending Sep. 2016). You will notice that Row 5 of each file contains "dates" which are formatted as "mmm yyyy." I think Ken's consolidation routine gets tripped up when I promote Row 5 to headers because the dates in each file are different. Is there a way to still perform the consolidation and retain the relationship of the respective dates with each month of financial data (perhaps unpivoting Columns C:O)? In doing so, it would also be important to create a custom column record the information in Row 1 of each attachment, which is the name & number of each unique asset. In my application, there are often several distinct assets, each with multiple Excel files for each year of profit & loss data.

It is worth noting that the information in Columns A:B of each file is often different as well. Column A is an account # from the owner's chart of accounts and Column B is the corresponding account name. Most accounting software is set up to suppress display of accounts that have no activity for each given time period. The attachment named "Account Codes" demonstrates this and also provides a master list of all account #s and names. I thought this might be helpful in setting up either a lookup table or the logic for a Choose formula. To me at least, setting up a Choose formula would be painfully laborious because the typical statement can be several hundreds rows of information. I'm curious as to your recommendation regarding this.

It would be very helpful to me (and I hope others) to understand how to best handle this situation. Cheers!
 

Attachments

  • Account Codes.xlsx
    11.5 KB · Views: 17
  • CarringtonRidge_OS_T12_09.2016.xlsx
    13.9 KB · Views: 16
  • CarringtonRidge_OS_YE_2015.xlsx
    13.9 KB · Views: 13
  • CarringtonRidge_OS_YE_2014.xlsx
    14.3 KB · Views: 15
  • CarringtonRidge_OS_YE_2013.xlsx
    14 KB · Views: 11
Hi Snew,

Here's a basic example of how to import a file and clean it up, I would take this further (remove decimal places and extra spaces, set data types etc) but it works as an example:

Code: 
//Query Called GetData
let


    Source = Excel.Workbook(File.Contents("C:\Excel\Power Query\ExcelGuru\6968\CarringtonRidge_OS_YE_2013.xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each [Kind] = "Sheet"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"Column1", "Column2", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
    Asset = #"Reordered Columns"{0}[Column1],
    #"Removed Top Rows" = Table.Skip( #"Reordered Columns",4),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows"),
    #"Filtered Rows2" = Table.SelectRows(#"Promoted Headers", each [Column1] <> null and [Column1] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"Total"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Column1", "Column2"}, "Month", "Amount"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Column1", "Account"}, {"Column2", "Description"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "AssetName", each Asset)
in
    #"Added Custom"

This can be converted to a function so that you pass it a list of workbooks and it will process all workbooks.

Code: 
//Query called GetData
(fileStr as text) as table =>
let
    Source = Excel.Workbook(File.Contents(fileStr), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each [Kind] = "Sheet"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"Column1", "Column2", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
    Asset = #"Reordered Columns"{0}[Column1],
    #"Removed Top Rows" = Table.Skip( #"Reordered Columns",4),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows"),
    #"Filtered Rows2" = Table.SelectRows(#"Promoted Headers", each [Column1] <> null and [Column1] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"Total"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Column1", "Column2"}, "Month", "Amount"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Column1", "Account"}, {"Column2", "Description"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "AssetName", each Asset)
in
    #"Added Custom"

Code: 
//Query called Data
let
    Source = Folder.Files("C:\Users\Comfy\Documents\Excel\Power Query\ExcelGuru\6968"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Extension], ".xls")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Folder Path", "Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "WorkbookData", each GetData([Folder Path] & [Name])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"WorkbookData"}),
    #"Expanded WorkbookData" = Table.ExpandTableColumn(#"Removed Other Columns1", "WorkbookData", {"Account", "Description", "Month", "Amount", "AssetName"}, {"Account", "Description", "Month", "Amount", "AssetName"})
in
    #"Expanded WorkbookData"

You won't need to create a lookup table for each year as in your example workbook but you will need to create a Master Lookup table if each subsidiary has their own chart of accounts. Is it possible to get the sources to map their CoA to a consolidated CoA?

I'm not quite sure yet what you are consolidating as you have mentioned "owners" and "assets", is the following accurate.

Multiple owners, with multiple assets, with one file for each Asset per financial year?


Finally, to produce a P&L from the Source data this post by Imke Feldmann might come in very handy: http://www.thebiccountant.com/2016/07/21/easy-profit-loss-account-scheme-reports-power-bi-power-pivot-dax/

Unfortunately, I don't have the appropriate version of Excel to utilise Power Pivot so can't assist you with that.
 
Comfy:

Thanks for your help and detailed response. I'll go through it carefully and see if I can get it to work for me. To answer your questions, I can typically get a consolidated CoA, which makes life a lot easier. The scenario I'm typically in involves one owner with one CoA and many assets with one file for each year. Based on that, I'd like to consolidate all of the files into one and use that to analyze them. It will be a lot easier and faster than going through one at a time.
 
In that case you won't need a consolidated chart of accounts.

The code I posted will help you to combine and clean the data and the link I posted should help you to create a P&L, one part of the process is to setup a template which includes all account codes and their parent (sub total)
 
Awesome! I'll let you know when I get it loaded up. Thanks again!
 
Comfy:

I was able to get this to work using your coding so once again thanks very much! I spent a fair amount of time reviewing it, which was very helpful. I'm curious about the advantage of your last piece of coding (for the data connection) vs. the Ken Puls method described in his Feb. 25, 2015 article

Also, how are you learning Power Query - any books, courses you'd recommend for a beginner?
 
You must log in or register to reply here.
Back
Top