Import multiple csv files that have different rows and each requires a custom column before merging

Hey,

I have over 100 csv files that I need to work with using Power BI. The problem is that many of the files have different amount of rows and different row titles. Additionally, each file contains information about the data the I need to transform into a new column.

I've been trying to use power query and I can solve my problem partly but I'm stuck with my beginner skills. Please help!

Here is an example of the table contained in the csv files:

All the rows and columns match in each file expect that I have different Categories in different files. As I need the Region info I need to add that to each file as a new column before joining them. I have managed to do that in power query with the following steps:

let
    Source = Csv.Document(File.Contents("C:\File X.csv"),[Delimiter=",", Columns=65, Encoding=932, QuoteStyle=QuoteStyle.None]),
    #"Added Custom" = Table.AddColumn(Source, "Region", each Source[Column1]{2}),
    #"Removed Top Rows" = Table.Skip(#"Added Custom",6),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Region"}}),
in
    #"Renamed Columns"

So how can I apply these steps to each file and then combine all of them into one table? I could do it all manually but it would take forever. I think I need to use import folder functions but cannot get it to work and I have no idea how to add the steps above.

I've tried to add the steps into this function, but cannot get it to work.

let
    Source = Folder.Files("C:\csv files"),
    Tables = List.Transform(Source[Content], each Table.PromoteHeaders(Csv.Document(_,null,null,null,1252))),
    SingleTable = Table.Combine(Tables)
in
    SingleTable

Thank you!