Excel append tables

I have a multiple excel files in one folder with the same metadata (structure). These tables are not proper tables, require some transformations. All the tables require the same steps to be converted into a normal tables. The goal here is to create a function that will transform all the tables into proper tables.

Data: There are 8 .xlsx files in the folder. Each file represents a seller. Each file has the same table structure...

Step 1: Load the 1st file, delete the 2nd applied step "Navigation". You don't need it.

Step2 : Add {0}[Data] to the code. This means that you want to get back column Data, 1st row. That record contains the whole table of data.

Step3: We have successfully retrieved the first table which needs to be transformed into a normal table. Step 3 will be something like Table.Transpose

Step4: I would bet that the best step after Transpose will be Promote Headers what we can simply achieve by adding Table.PromoteHeaders to the code. This function require just 1 argument which is a table.

Step5: The next necessary step that we must take is Unpivot. We can use Table.UnpivotOtherColumns function which will do it for us. This function takes 4 simple arguments. "as table" on the end is optional. We are nearly finished with the transformation and it takes just 1 step so far...that's just fantastic, isn't it ? ? ? (Give like - subscribe)

Syntax:

Table.UnpivotOtherColumns(table as table, pivotColumns as list, attributeColumn as text, valueColumn as text) as table 

Step6: Correct the data types with Table.TransformColumnTypes function. It needs 2 arguments but the 2nd one is optional. We apply just the list within a list to define our data types.

Syntax:

Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table

Step7: Build the function. Replace the highlighted part of the code with the variable InputTable

The code should looks like this:

(InputTable)=>

let

Source = Table.TransformColumnTypes

(Table.UnpivotOtherColumns

( Table.PromoteHeaders

(Table.Transpose

(Excel.Workbook

(InputTable,

null, true){0}[Data])),{"Date","Product"},"Subcateory","Units"),{{"Date", type date},{"Product", type text},{"Units",Int64.Type }}) as table

in

Source

This is the function I have just build. And maybe now is the time to give it some meaningful name. Or maybe later...

Step8: New source > File > Folder

Let's import the files we need to transform into a normal tables.

Step9: Transform > Extract > Text before delimiter

These names might be very important for our data. Maybe some sellers, who knows.

Step10: Highlight Content and Name columns and remove other columns. Easy!

Step11: Add Column > Invoke Custom Function (And maybe now is the time to give it some meaningful name?)

The good news is that the function does what we needed from it.

Step12 : Remove column Content, expand column Carl, keep all columns and fix the data types. In the Step6 when I have added Table.TransformColumnTypes function to change the data types, this step is not needed. It does not have any effect on the data.

Final step: Load the data into worksheet (data model...anywhere)