Power Query errors when new month

[GorD]

I have crated a query that processes a txt file download from SAP. It formats the report, adds a few coulmns from other tables etc, and for 3 weeks of the month works great. It has columns for, past, current month ( currently called 09 2016), the next 11, months in same format and then a column for future.

These columns should all be numerical values and one of the steps formats these to whole numbers, from (e,g. 99.000 to 99)

In a new month the query fails as it doesn't find a column named 09 2016.

Can't help but think this should be easy and i am missing something obvious. How do i get this to run every week without error

Thanks

 

[ImkeF]

Having difficulties to understand where the difference to the (running) previous month lies. Do you mean that the columns-reference shall change from 08 2016 to 09 2016 and it doesn't (in general)?
Have a look at your code if it contains a "hardcoded" reference to a column like 08 2016: You have to rewrite that and create a logic that does without.
You can paste your code (of the complete query) here, if you need help on that.

One tip: Never use blanks in column names. It makes your code horrible to edit.

 

[GorD]

Thanks, i'll get the code on Monday when i'm next back at work.

To try to explain this month i have columns named 09 2016 to 08 2017, as part of the query i have selected all these columns and changed the data type to whole number.

However, when we get into October there will no longer be a column called 09 2016 and the query fails - i need to go into the code and manually change the column names. It does have hard coded column references and i'm not sure how i would go about amending this. Is part oof the problem here using excel tables?

Thanks again

 

[Dan80]

If you unpivot the columns then that would solve the problem. Instead of having 12 columns for 12 months just have 2 columns one for the month and the other with the values. This way when a month 'drops off' the query doesn't fail as it isn't an individual column which the query can't find, if that makes any sense! Good luck

 

[GorD]

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

 

[ImkeF]

You start of as Dan80 suggested and unpivot other columns than your month names, then
-> change the types of the (now only one) value column
-> Pivot back
-> Get list of current month names using List.Difference https://msdn.microsoft.com/en-us/library/mt253608.aspx
-> Replace your hardcoded month names in the reorder-step by that list

    UnpivotNonMonths = Table.UnpivotOtherColumns(#"Removed Columns2", {"Material", "Material Description", " WIP Quantity", "Future Quantity", "WBS_MOD", "Contract", "QNotes-all"}, "Attribute", "Value"),
    ChangeTypeMonthsValues = Table.TransformColumnTypes(UnpivotNonMonths,{{"Value", type number}}),
    PivotBack = Table.Pivot(ChangeTypeMonthsValues, List.Distinct(ChangeTypeMonthsValues[Attribute]), "Attribute", "Value", List.Sum),
    GetCurrentMonthsNames = List.Difference(Table.ColumnNames(PivotBack), {"Material", "Material Description", " WIP Quantity", "Future Quantity", "WBS_MOD", "Contract", "QNotes-all"}),
    #"Reordered Columns" = Table.ReorderColumns(PivotBack, List.Combine({{"Material", "Material Description", " WIP Quantity"}, GetCurrentMonthsNames, {"Future Quantity", "WBS_MOD", "Contract", "QNotes-all"}}))
in
    #"Reordered Columns"

The columns with the month names will be sorted in the same order as they have been delivered originally in your table.