Table.Expand duplicates some Files when using folder connect? Tried everything I know.

[shanestocks]

Hi all. I am using a Query that looks at sub-folders within a main folder (using folder.contents). I am then using a date filter on the folder names to obtain only the folders we need.

I am then getting all of the files in those folders (usually 2 folders at max) and expanding. What happens is so puzzling. The vast majority of the files are fine, but 1,2, or sometimes 3 of the files, duplicate, and even add random rows! I have no idea what to do and it's driving me crazy. Any help would be great appreciated.

I have attached screenshots and below is the source data. I have performed transformations in the sample file area. It displays correctly in the sample file, but not in the final result! How is this even possible??

Final result of one of the files in question

...but then randomly doubles and adds a random row after expanding table?

...But file returns absolutely fine in the sample file?

Data looks good in the sample file

Query code:

let
    Source = Folder.Contents("G:\Finance\Karro Group Finance\FP&A\Net Working Capital\Stock Report - WIP\Submission Data\FY24"),
    dateConversion = Table.NestedJoin(Source, {"Name"}, dateRange, {"WeekNo"}, "dateRange", JoinKind.LeftOuter),
    expandDateConversion = Table.ExpandTableColumn(dateConversion, "dateRange", {"WeekDate"}, {"WeekDate"}),
    fromWeek = Table.SelectRows(expandDateConversion, each [WeekDate] >= parameters[fromDate]{0}),
    toWeek = Table.SelectRows(fromWeek, each [WeekDate] <= parameters[toDate]{0}),
    removeWeekFilter = Table.RemoveColumns(toWeek,{"WeekDate"}),
    #"Expanded Content" = Table.ExpandTableColumn(removeWeekFilter, "Content", {"Content", "Name", "Extension"}, {"Content.1", "Name.1", "Extension.1"}),
    pqAuto1 = Table.SelectRows(#"Expanded Content", each [Attributes]?[Hidden]? <> true),
    pqAuto2 = Table.AddColumn(pqAuto1, "Transform File", each #"Transform File"([Content.1])),
    checkSubmissionsStage = Table.SelectColumns(pqAuto2, {"Transform File"}),
    expandTable = Table.ExpandTableColumn(checkSubmissionsStage, "Transform File", {"week", "matCode", "matDesc", "site", "cluster", "div", "company", "plant", "stockLocation", "uom", "protein/species", "matType", "matCat", "fresh/frozen", "qty", "weight", "value"}, {"week", "matCode", "matDesc", "site", "cluster", "div", "company", "plant", "stockLocation", "uom", "protein/species", "matType", "matCat", "fresh/frozen", "qty", "weight", "value"}),
    Custom1 = Table.SelectRows(expandTable, each [site] = "Bloor"),
    changeType = Table.TransformColumnTypes(Custom1,{{"week", type text}, {"site", type text}, {"cluster", type text}, {"div", type text}, {"company", type text}, {"plant", type text}, {"stockLocation", type text}, {"matCode", type text}, {"matDesc", type text}, {"uom", type text}, {"protein/species", type text}, {"matType", type text}, {"matCat", type text}, {"fresh/frozen", type text}, {"qty", type number}, {"weight", type number}, {"value", type number}}),
    replaceFY23error = Table.ReplaceValue(changeType,"FY23","FY24",Replacer.ReplaceText,{"week"}),
    replaceIngredients = Table.ReplaceValue(replaceFY23error,"Ingredients","Ingredient",Replacer.ReplaceText,{"matType"})
in
    replaceIngredients

Sample file code

let
    Source = Excel.Workbook(Parameter1, null, true),
    Input_Sheet = Source{[Item="Input",Kind="Sheet"]}[Data],
    renameColumns = Table.RenameColumns(Input_Sheet,{{"Column1", "week"}, {"Column2", "site"}, {"Column3", "cluster"}, {"Column4", "div"}, {"Column5", "company"}, {"Column6", "plant"}, {"Column7", "stockLocation"}, {"Column8", "matCode"}, {"Column9", "matDesc"}, {"Column10", "uom"}, {"Column11", "protein/species"}, {"Column12", "matType"}, {"Column13", "matCat"}, {"Column14", "fresh/frozen"}, {"Column15", "qty"}, {"Column16", "weight"}, {"Column17", "value"}}),
    removeOtherColumns = Table.SelectColumns(renameColumns,{"week", "site", "cluster", "div", "company", "plant", "stockLocation", "matCode", "matDesc", "uom", "protein/species", "matType", "matCat", "fresh/frozen", "qty", "weight", "value"}),
    removeSiteRecord = Table.SelectRows(removeOtherColumns, each ([site] <> "Site")),
    customFilter = Table.SelectRows(removeSiteRecord, each [week] <> null and [week] <> "X" and [week] <> ""),
    #"Reordered Columns" = Table.ReorderColumns(customFilter,{"week", "matCode", "matDesc", "site", "cluster", "div", "company", "plant", "stockLocation", "uom", "protein/species", "matType", "matCat", "fresh/frozen", "qty", "weight", "value"}),
    textTransforms = Table.TransformColumns(#"Reordered Columns",{{"protein/species", Text.Proper, type text}, {"matType", Text.Proper, type text}, {"matCat", Text.Proper, type text}, {"fresh/frozen", Text.Proper, type text, "week", Text.Upper, type text}}),
    removeNullValue = Table.SelectRows(textTransforms, each [value] <> null and [value] <> ""),
    replaceNulls = Table.ReplaceValue(removeNullValue,null,0,Replacer.ReplaceValue,{"qty", "weight"}),
    replaceMatCode = Table.ReplaceValue(replaceNulls,null,"None Given",Replacer.ReplaceValue,{"matCode","matDesc","uom","protein/species"}),
    replaceCategoryErrors = Table.ReplaceErrorValues(replaceMatCode, {{"matCat", "N/A"}, {"fresh/frozen", "N/A"},{"qty", 0},{"weight", 0},{"value", 0}})
in
    replaceCategoryErrors

[Anonymous]

Hi @shanestocks 

According to your description, there are subfolders in your home folder, and you have to extract files from the subfolders, which is nested, and when you expand all the files, it will expand all the main folders and subfolders, so there will be many more lines. 

Best Regards!

Yolo Zhu