Imported columns are 1, 10, 100, 101 ... instead of 1, 2, 3, 4 ...

[Monsignor]

What is a way to correct this when the dataset needs to be imported in numerical order, and not the order shown in the image below?

 

[MarcelBeug]

First I would like to remark that this looks strange to me: usually you would get the Columns in the same order as in the source table.

Anyhow, my suggestion is to add a column with your columns sorted on number.
In the code below, #"Renamed Columns" is the name of the preceding step and then proceed from there.

    ColumnsSorted = Table.AddColumn(#"Renamed Columns","ReorderedTable", each 
                    Table.ReorderColumns([Import.Data],
                    let
                        ColumnNames = Table.ColumnNames(#"Renamed Columns"[Import.Data]{0}),
                        ColumnNumbers = List.Transform(ColumnNames, each Number.From(Text.Replace(_, "Column",""))),
                        #"Sorted Items" = List.Sort(ColumnNumbers,Order.Ascending),
                        ColumnNamesSorted = List.Transform(#"Sorted Items", each "Column"&Text.From(_))
                    in
                        ColumnNamesSorted))

 

[Monsignor]

Marcel, this is certainly weird. I saw this problem months ago and it went away. Now I'm working with someone and she's getting this problem. I don't know if it's because the source file is a .xls or what.

 

[MarcelBeug]

That question becomes too technical for me.
Otherwise I'm curious if my solution worked for you.

 

[Matt Allington]

I have definitely seen this strange behaviour before too.

 

[Monsignor]

Marcel, sorry. I'm having a hard time even reproducing the problem. So, I haven't been able to apply your solution. This is the strangest thing. I'm at a loss.