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??

Works great in the transform file in one of the files. 14 rows is correct.

When I expand tables from the folder the query, it duplicates and even adds an extra row!

Here is the 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

And the code in the sample transform file:

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

 

[GraH]

Purely going by the code, then this step might be the cause of duplication.

 dateConversion = Table.NestedJoin(Source, {"Name"}, dateRange, {"WeekNo"}, "dateRange", JoinKind.LeftOuter),

Another possibility is that the source files sometimes may contain named ranges or tables. Then PQ "sees" the data twice, once on the sheet level and a second time as named range or table. You can filter on the Kind column.
Any other possibility I do not know yet.