shanestocks
New Member
- Joined
- Jan 9, 2024
- Messages
- 1
- Office Version
- 365
- Platform
- 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??
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:
And the code in the sample transform file:
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:
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
replaceIngredientsAnd 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
