SAP (.txt) file and Power Query processing

[GorD]

I have a report that is downloaded from SAP and saved as latest.txt. I have 3 other tables that I need to merge various columns from.

Power Query loads all these tables and does the merge - all seemed to be working great.

However, the SAP report has monthly demand for various products and the column headings are the month and year from the current month plus the next 12 future months.

So, at present the first month column is called 05/2017, but next month there will be no column named this and first column will be 06/2017.

Because of this my report fails every time we move into a new month

I have tried leaving promoting headers until as late as possible , but i still can't re-order columns or change data type without error.

How can I get the report to work with changing column headers every month



Thanks

 

[Comfy]

Hi GorD,

Without specifics over what you are re-ording and how I can't really provide much more than:

Unpivoting should help with being able to set the data type of the Monthly Demand.

Do you have an example that we could work from?

 

[GorD]

This 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 05/2017", " M 06/2017", " M 07/2017", " M 08/2017", " M 09/2017", " M 10/2017", " M 11/2017", " M 12/2017", " M 01/2018", " M 02/2018", " M 03/2018", " M 04/2018", "Future Quantity", "WBS_MOD", "Contract", "QNotes-all"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{" WIP Quantity", Int64.Type}, {" M 05/2017", Int64.Type}, {" M 06/2017", Int64.Type}, {" M 07/2017", Int64.Type}, {" M 08/2017", Int64.Type}, {" M 09/2017", Int64.Type}, {" M 10/2017", Int64.Type}, {" M 11/2017", Int64.Type}, {" M 12/2017", Int64.Type}, {" M 01/2018", Int64.Type}, {" M 02/2018", Int64.Type}, {" M 037/2018", Int64.Type}, {" M 04/2018", Int64.Type}, {"Future Quantity", Int64.Type}})
in
#"Changed Type"

The last two stages are where it fails due to the column names

Unpivoting would possible correct the data type issue, need to give that a go - any ideas for re-ordering columns

Thanks

 

[Comfy]

Now I've had a chance to test it, unpivoting will do both.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Input Table
2
3	Material	Material Description	WIP Quantity	M 08/2017	M 07/2017	M 06/2017	M 05/2017	Future Quantity	WBS_MOD	Contract	Qnotes-all
4	aqsd	aqsd	5	5	5	5	5	5	5	asc	asc
5	wrgrebr	wrgrebr	5	5	5	5	5	5	5	asc	asc
6	tetb	tetb	5	5	5	5	5	5	5	asc	asc
7	gertetrvb	gertetrvb	5	5	5	5	5	5	5	asc	asc
8
9
10	Output Table
11
12	Material	Material Description	WIP Quantity	Future Quantity	WBS_MOD	Contract	Qnotes-all	M 05/2017	M 06/2017	M 07/2017	M 08/2017
13	tetb	tetb	5	5	5	asc	asc	5	5	5	5
14	aqsd	aqsd	5	5	5	asc	asc	5	5	5	5
15	gertetrvb	gertetrvb	5	5	5	asc	asc	5	5	5	5
16	wrgrebr	wrgrebr	5	5	5	asc	asc	5	5	5	5
17
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Material", "Material Description", "WIP Quantity", "Future Quantity", "WBS_MOD", "Contract", "Qnotes-all"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Value", Int64.Type}, {"Material", type text}, {"Material Description", type text}, {"WIP Quantity", Int64.Type}, {"Future Quantity", Int64.Type}, {"WBS_MOD", Int64.Type}, {"Contract", type text}, {"Qnotes-all", type text}, {"Attribute", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

Is the position of [Contract] and [Qnotes-all] important?

If you want to keep the Order of the [Material] column you could add an Index and then sort by that once pivoted.

 

[GorD]

Thanks for taking the time to look at this.

The Contract and Qnotes fields are being added from other tables and as such appeared at the end, after the demand, but i don't suppose its that critical. Sorting material isn't critical.

Can i just copy and paste your code in - if so after which step

If not i can just go through the process - many thanks

 

[GorD]

In your example do you lose or change a column name - in a months time there will be no col called 05/2017 and will the unpivoting cope with this. Sorry if you have covered this already

 

[Comfy]

Thanks for taking the time to look at this.

The Contract and Qnotes fields are being added from other tables and as such appeared at the end, after the demand, but i don't suppose its that critical. Sorting material isn't critical.

Can i just copy and paste your code in - if so after which step

If not i can just go through the process - many thanks

Try this, you will probably need to amend some of the column names I've used.

et
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"}), 
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns2", {"Material", "Material Description", "WIP Quantity", "Future Quantity", "WBS_MOD", "Contract", "Qnotes-all"}, "Attribute", "Value"),
#"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Value", Int64.Type}, {"Material", type text}, {"Material Description", type text}, {"WIP Quantity", Int64.Type}, {"Future Quantity", Int64.Type}, {"WBS_MOD", Int64.Type}, {"Contract", type text}, {"Qnotes-all", type text}, {"Attribute", type text}}),
 #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

In your example do you lose or change a column name - in a months time there will be no col called 05/2017 and will the unpivoting cope with this. Sorry if you have covered this already

I don't understand the question. It doesn't matter which Month columns are added or removed because we use "Unpivot Other Columns".
We don't reference any Month columns, we only reference the columns that are persistent.

 

[GorD]

Great, I'll give taht a go on Monday when i'm back at work

Thanks for your help

 

[GorD]

Hi I am getting an error saying the column Wip Quantity wasn't found - I can see this column which is column 2 in my data at the previous step, so I think it is to do with the order of the columns. Do they need to be consecutive columns for the unpivot other columns. One of the issues I had previously was with trying to re- order the columns - so think I need to do this before promoting headers.

Hope this makes some sense (not sure it does)

 

[GorD]

This is at the unpivoted other columns stage