Cleaning the data in power query editor for multiple files from a folder

 

* There clearly has gone a lot of effort in creating that Excel file format. Maybe a smaller effort can go into producing a flat table output that can then be loaded into Power BI.  But if I had to import the data I would use this approach:

 

 

 

let
    Source = Folder.Files(fld),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.EndsWith([Name], "type of serv.xlsx")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
    #"Split Column by Positions" = Table.SplitColumn(#"Removed Other Columns", "Name", Splitter.SplitTextByPositions({0,8}), {"Period"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Positions", "Data", each Excel.Workbook([Content]){[Item="Sheet1",Kind="Sheet"]}[Data]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Contract Number", each Text.Range([Data][Column1]{0},17)),
    Convert = (tbl)=>
    let #"Added Custom2" = Table.AddColumn(tbl, "Relation", each if Text.StartsWith([Column1],"Relation : ") then Text.Range([Column1],11) else null),
        #"Filled Down" = Table.FillDown(#"Added Custom2",{"Relation"}),
        #"Added Custom3" = Table.AddColumn(#"Filled Down", "Experience Group", each if Text.StartsWith([Column1],"Experience Group : ") then Text.Range([Column1],19) else null),
        #"Filled Down1" = Table.FillDown(#"Added Custom3",{"Experience Group"}),
        #"Added Custom4" = Table.AddColumn(#"Filled Down1", "Category", each if [Column2] = null then [Column1] else null),
        #"Filled Down2" = Table.FillDown(#"Added Custom4",{"Category"}),
        #"Filtered Rows3" = Table.SelectRows(#"Filled Down2", each [Column1] <> "            " and not Text.Contains([Column1], "Total") and [Column2] <> null and not Text.Contains([Column1], "Experience Group") and not Text.StartsWith([Column1], "Relation : ")),
        #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows3",{{"Column1", "Subcategory"}, {"Column2", "Current Submitted"}, {"Column3", "Current Eligible"}, {"Column4", "Current Paid"}, {"Column5", "Current % Paid"}, {"Column7", "Prior Submitted"}, {"Column8", "Prior Eligible"}, {"Column9", "Prior Paid"}, {"Column10", "Prior % Paid"}})
    in
        #"Renamed Columns",
    #"Added Custom5" = Table.AddColumn(#"Added Custom1", "Converted", each Convert([Data])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom5",{"Period", "Contract Number", "Converted"}),
    #"Expanded Converted" = Table.ExpandTableColumn(#"Removed Other Columns1", "Converted", {"Subcategory", "Current Submitted", "Current Eligible", "Current Paid", "Current % Paid", "Prior Submitted", "Prior Eligible", "Prior Paid", "Prior % Paid", "Relation", "Experience Group", "Category"}, {"Subcategory", "Current Submitted", "Current Eligible", "Current Paid", "Current % Paid", "Prior Submitted", "Prior Eligible", "Prior Paid", "Prior % Paid", "Relation", "Experience Group", "Category"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Converted",{{"Current Submitted", Currency.Type}, {"Current Eligible", Currency.Type}, {"Current Paid", Currency.Type}, {"Current % Paid", Percentage.Type}, {"Prior Submitted", Currency.Type}, {"Prior Eligible", Currency.Type}, {"Prior Paid", Currency.Type}, {"Prior % Paid", Percentage.Type}})    
in 
#"Changed Type"