Power Query swaps one column data with other column

[HasanK]

I realised strange situation while importing customer weekly sales reports from folder. It looks work fine, no error at the begining then I realised the case only for one week data, city column data and model data completely swap places, I mean city data on model column data, model column data on city data for only this week, all other 2 years week data are ok, report sheet formats are exactly same. I put screen shoots. Is there anyone face similar situation and how to solve? 

let
Source = Folder.Files("C:\Users\XYZ"),
#"Added Custom" = Table.AddColumn(Source, "WBOBJ", each Excel.Workbook(File.Contents([Folder Path]&"\"&[Name]),null, true)),
#"Expanded WBOBJ" = Table.ExpandTableColumn(#"Added Custom", "WBOBJ", {"Name", "Data"}, {"WBOBJ.Name", "WBOBJ.Data"}),
#"Expanded WBOBJ.Data" = Table.ExpandTableColumn(#"Expanded WBOBJ", "WBOBJ.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}, {"WBOBJ.Data.Column1", "WBOBJ.Data.Column2", "WBOBJ.Data.Column3", "WBOBJ.Data.Column4", "WBOBJ.Data.Column5", "WBOBJ.Data.Column6", "WBOBJ.Data.Column7", "WBOBJ.Data.Column8"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded WBOBJ.Data",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "WBOBJ.Name"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Removed Blank Rows" = Table.SelectRows(#"Removed Top Rows", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Invoice Date", type date}, {"€", type number}, {"Unit", type number}, {"€_1", type number}, {"Tax Number", type text}, {"Model", type text}, {"City", type text}, {"Reseller Name", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Model"}, Table2, {"Model"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Model NO"}, {"Table2.Model NO"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Table2",{"Invoice Date", "Reseller Name", "Tax Number", "City", "Model", "Table2.Model NO", "€", "Unit", "€_1"}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Reordered Columns", {"Unit"})
in
#"Removed Errors"

[AllisonKennedy]

This sounds like a problem with your excel data source. Check the file for that one week and you'll see that the two columns were reorderd for that one file only. Easy fix is to swap their order around the right way in the source.

You could potentially fix this by promoting the headers inside the sample Excel files individually first and then combining the files, so using a custom function rather than just the custom column you currently have. This option would require all the Excel columns to be spelled exactly the same in each file. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[HasanK]

Hello AlliSon,

Thanks for advice but I did, but the reason of the issue seems different. (I am thinking on whether Greek language may cause?)

There is no differnce between source with others, LEFT image is problematic one (2 weeks), right one is normal (102 weeks). I am struggling still..

[AllisonKennedy]

Can you please share the code that is in the Advanced Editor of the original screenshot you shared? 

If you are using a custom function we may need to see the code for that as well. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[HasanK]

I shared the M code in my first post but if it is not enough I can send again... I find a manual way, I coppied the data to completly new excel sheet without and save then update, currently it worked, but of course it is annoying...

Thanks ,