---

Preparation and Setup

Tools used for automation: Excel, Power Query, & M Code
Created two folders, respectively for Inventory Data and the Consolidated Data

Contents of the Inventory Folder:

---

Loading the data

I created a new Excel workbook and saved it in the Consolidated Data folder. I then opened the Power Query to create a variable for the local path of the source data (Inventory Folder)

Consolidating Data

I then started to create a blank query to use the variable folder I created earlier

A closer snapshot of the workbooks has been fetched inside the folder. The next step is to have these workbooks stored in a table along with their contents

The next step is to transform the columns of the table, and we can do that by using the Table.TransformColumns function (M code)

This is to ensure that columns are standardized before the appending process takes place

Now for the final query. We will need to combine all the data. We will be using the Table.Combine function

Let’s load the data as a table to finalize it

We consolidated a total of 245,848 rows. To verify this, I have taken a snapshot of each dataset

For Food and Beverage Inventory 2022, we have a total of = 91594,
For Food and Beverage Inventory 2023, we have a total of = 154254
Total of 245,848

Once the new inventory data for 2024 is available, the end user can simply upload it to the folder to trigger the automation.

If you want to follow the same approach, I've included the code I used for this method.

let

Source = Folder.Contents(FolderLocation), FetchedWorkbooks = Table.AddColumn(Source, "Workbooks", each Excel.Workbook([Content])), #"Expanded Workbooks" = Table.ExpandTableColumn(FetchedWorkbooks, "Workbooks", {"Name", "Data", "Item", "Kind", "Hidden"}, {"FileName", "Data", "Item", "Kind", "Hidden"}), #"Filtered Item = Sales" = Table.SelectRows(#"Expanded Workbooks", each ([Item] = "Sales")), ExtractedTables = Table.AddColumn(#"Filtered Item = Sales", "TransformedData", each Table.PromoteHeaders([Data])), StandardizedColumns = Table.TransformColumns(ExtractedTables, {"TransformedData", each Table.RenameColumns(_, {{"OldColumn1", "NewColumn1"}, {"OldColumn2", "NewColumn2"}}, MissingField.Ignore)}), CombinedData = Table.Combine(StandardizedColumns[TransformedData])

in
CombinedData

To fully utilize the code, please make sure to follow these instructions:

1. Make sure to create a folder for the inventory data

2. Make sure to have a uniform worksheet name in every workbook (Sales, Inventory, etc.)

3. Make sure to delete empty rows before the data in each workbook

Results

Once implemented, this solution will streamline data consolidation, reducing manual effort and minimizing human error. By ensuring accurate and timely data, it will empower data-driven decision-making, ultimately enhancing strategic planning and optimizing resource allocation within the organization