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

shanestocks

New Member
Joined
Jan 9, 2024
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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??

TransformFile.png


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

stockData.png


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

Here is the code:

VBA 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:

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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Purely going by the code, then this step might be the cause of duplication.
Power Query:
 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.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top