Hi, I temporarily forgot about this as it's only an issue when a new month starts.
Here is the code
let
Source = Table.FromColumns({Lines.FromBinary(File.Contents("O:\Supply Chain\Susan B\DIRECT LINE FEED\Latest DLFF.txt"),null,null,1252)}),
#"Split Column by Delimiter" = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv),{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16", "Column1.17", "Column1.18", "Column1.19"}),
#"Removed Top Rows" = Table.Skip(#"Split Column by Delimiter",2),
#"Filtered Rows" = Table.SelectRows(#"Removed Top Rows", each ([Column1.4] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1.1", "Column1.3"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"Column1.5"},WBS_Changes,{"Column1"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Column2"}, {"NewColumn.Column2"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn",{{"NewColumn.Column2", "WBS Mod"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null, each _[Column1.5], Replacer.ReplaceValue,{"WBS Mod"}),
#"Removed Columns1" = Table.RemoveColumns(#"Replaced Value",{"Column1.5"}),
#"Merged Queries1" = Table.NestedJoin(#"Removed Columns1",{"WBS Mod"},WBS_Contract,{"WBS"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"Contract"}, {"NewColumn.Contract"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded NewColumn1",{{"NewColumn.Contract", "Contract"}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Renamed Columns1"),
#"Renamed Columns2" = Table.RenameColumns(#"Promoted Headers",{{"WBS", "WBS_MOD"}, {"Column18", "Contract"}}),
#"Merged Queries2" = Table.NestedJoin(#"Renamed Columns2",{"Material"},QNotesFile,{"Material"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn2" = Table.ExpandTableColumn(#"Merged Queries2", "NewColumn", {"Material Description", "QNotes-all"}, {"NewColumn.Material Description", "NewColumn.QNotes-all"}),
#"Renamed Columns3" = Table.RenameColumns(#"Expanded NewColumn2",{{"NewColumn.QNotes-all", "QNotes-all"}, {"NewColumn.Material Description", "Material Description"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns3",{"Plant"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"Material", "Material Description", " WIP Quantity", " M 09/2016", " M 10/2016", " M 11/2016", " M 12/2016", " M 01/2017", " M 02/2017", " M 03/2017", " M 04/2017", " M 05/2017", " M 06/2017", " M 07/2017", " M 08/2017", "Future Quantity", "WBS_MOD", "Contract", "QNotes-all"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{" WIP Quantity", Int64.Type}, {" M 09/2016", Int64.Type}, {" M 10/2016", Int64.Type}, {" M 11/2016", Int64.Type}, {" M 12/2016", Int64.Type}, {" M 01/2017", Int64.Type}, {" M 02/2017", Int64.Type}, {" M 03/2017", Int64.Type}, {" M 04/2017", Int64.Type}, {" M 05/2017", Int64.Type}, {" M 06/2017", Int64.Type}, {" M 07/2017", Int64.Type}, {" M 08/2017", Int64.Type}, {"Future Quantity", Int64.Type}})
in
#"Changed Type"
Its the last two stages where the column names are hard coded that cause the problem - how would I change?
DAN80, this gives a table of data that goes out to supplier, so list format wouldn't work, but thanks