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]

Power Query is case sensitive.

My Code has "WIP Quantity" which isn't the same as "Wip Quantity"

Update the code or column header and try again.

 

[GorD]

I'm afraid that's just a typo by me in the post looking further back field name is WIP QTY

 

[Comfy]

So did you change the code so that it reads as "WIP QTY" instead of "WIP Quantity"

 

[GorD]

No the code always said WIP, i just typed it as Wip in the thread post 9

 

[Comfy]

Hi GorD,

I'm lost now so let's start again with the latest problem.

This is the latest 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"}), 
#"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"

What error do you get when you use this?
Is it related to the highlighted columns?

 

[GorD]

I have it working so thanks for your help.

I ended up copying in the code above deleting the lines from Unpivot( which still gave col not found errors) and redoing these steps.

Prior to this I tried inserting clean and trim operations and renaming cols.

But bottom line is it is working so thanks for your help - much appreciated