Hello @Iwona ,
- Don’t work manually on your files. You can leave them untouched.
- Do not add any 0 to your data. Normally you filter all 0 values out. I did in the code below!
- Make sure that the file date is within the first month of your data (start month). I noticed that you have data some days before the download date. Normally that is no problem, but if you are near a month change make sure that the download month matches the month of the first data entries.
- Have all of your 20 data files in one dedicated folder. It makes your life easier if you do not have to filter for these 20 files (difficult to identify as they all have different names).
- Add a query fxRunningTotal. Add a blank query, copy the below code in the Advanced Editior and name the query fxRunningTotal. This query (function) will give you the information how many months are added after the start month:
(grouping as list) as list =>
let
GRTList = List.Generate(
()=> [ GRT = 0, i = 0 ],
each [i] < List.Count(grouping),
each try
if grouping{[i]} = grouping{[i] + 1}
then [GRT = [GRT], i = [i] + 1]
else [GRT = [GRT] + 1, i = [i] + 1]
otherwise [i = [i] + 1],
each [GRT]
)
in
GRTList
- Use this code, but adapt the FolderPath. What you will get is a rolling data base, which takes automatically care of changing years.
let
Source = Folder.Files("C:\YourPathToTheDedicatedFolder\"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Data"}, {"Data"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Download", "Location"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Download", type date}, {"Location", type text}}),
#"Calculated Start of Month" = Table.TransformColumns(#"Changed Type1",{{"Download", Date.StartOfMonth, type date}}),
#"Replaced Value" = Table.ReplaceValue(#"Calculated Start of Month",".xlsx","",Replacer.ReplaceText,{"Location"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Replaced Value", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32"}, {"Grade", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31"}),
#"Added Month" = Table.AddColumn(#"Expanded Data", "Month", each if List.Contains({"January", "February","March","April","May","June","July","August","September","October","November","December"},[Grade]) then [Grade] else null),
#"Filled Down" = Table.FillDown(#"Added Month",{"Month"}),
#"Filtered Rows4" = Table.SelectRows(#"Filled Down", each ([Grade] <> null and [Grade] <> "Grade" and [Grade] <> "Total" and [Grade] <> [Month])),
#"Running Month" = Table.FromColumns(
Table.ToColumns(#"Filtered Rows4") & {fxRunningTotal( List.Buffer(#"Filtered Rows4"[Month]) )},
Table.ColumnNames(#"Filtered Rows4") & {"Running Month"}
),
#"Added Date1" = Table.AddColumn(#"Running Month", "Date1", each Date.AddMonths([Download], [Running Month]), type date),
#"Removed Columns1" = Table.RemoveColumns(#"Added Date1",{"Running Month"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Download", "Location", "Grade", "Month", "Date1"}, "Day", "Number of employees"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Number of employees", Int64.Type}}),
#"Filtered 0" = Table.SelectRows(#"Changed Type", each ([Number of employees] <> 0)),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Filtered 0", {{"Day", each Number.From(Text.AfterDelimiter(_, "Column")), Int64.Type}}),
#"Added Date" = Table.AddColumn(#"Extracted Text After Delimiter", "Date", each Date.AddDays([Date1], [Day]-1), type date),
#"Removed Other Columns2" = Table.SelectColumns(#"Added Date",{"Date", "Location", "Grade", "Number of employees"})
in
#"Removed Other Columns2"
You can load the result to a table, pivot table, pivot graph or the data model.
Do not just slice by month name. 
Regards,
Matthias
I checked everything 
help.