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

[amikm]

Hi All,

I am struggling to get data for 20-21 Q2 for the Type of Service file in my Power BI report after applying a series of transformations, the data is getting filtered out for Relation: All  during one of the Transformation,  I think near these steps

The data for 20-21 Q1 for the Type of Service file is coming perfectly in the report.

Note: I am getting the data for both the files coming from a single folder. 

I am expecting the output for Report period 20-21 Q2 file Similar to Report Period 20-21 Q1, But I can see in data for Report period 20-21 Q2, the data is missing for  Relation "All". But the raw file has data for this as well.

Please find the attached pbix where I am facing the issue.

[lbendlin]

* 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"

[amikm]

Hi @lbendlin, Thanks for looking into the problem. Could you please share the pbix /excel as well?  for me final output is empty If I am using this in my power query editor as a blank query.

Also,  for me, I am not able to see any expression error in my steps as per your screenshot

Thanks

[lbendlin]

pbix attached. Change folder parameter as needed.