Expanding table scraped from web with more than 9 columns - cols are out of sync

[bstirling]

Any ideas how to properly sequence the columns when using Table.ExpandTableColumn to sequence the columns as they come in? And not by Column 1, Column10, Column11, ... Column2, Column20, Column21, ... Column3?

What I did was reorder the columns manually but that seems crazy to have to do. Power Query should not sort the columns in that order since it knows there are Cols 2-9 there!.

Thanks for your advice!

 

[Dan Bliss]

When there are lots of columns to move around, I edit directly in the Advanced editor, but use GUI to start.
Here are details:
Use the Graphic User Interface to move just one column (doesn't matter which one). This creates a new step, "Reordered Columns" in Applied Steps.
Then under Home tab, click Advanced Editor, and find the new step, just created, probably #"Reordered Columns" on the last row before "in".
In #"Reordered Columns", cut and paste fields in the order you want them. (If this is too confusing, try cutting the whole #"Reordered Columns" line to Notepad or your favorite simple text editor, and edit within there. When done paste the revised over the unrevised in Power Query Advanced Editor. Be careful when moving things around to maintain double-quotes before and after and a comma separating each column name.

You don't mention if there are any column names in the data. If there are, do promote names before reordering columns. Or rename columns to anything more informative than Col1, Col2, etc. This helps when reordering. Sorry, I can't think of any way of forcing PowerQuery to autoname Col2-9 to Col02-Col09, which would get you the correct order.

 

[MarcelBeug]

If all your columns have default names Column1, -2 etcetera, then you can reorder them with (YourTable is the name of the step after which you insert this code):

= Table.ReorderColumns(YourTable, Table.ColumnNames(#table(Table.ColumnCount(YourTable),{})))

The #table function can be used in various ways; in this example it creates a table with the number of columns according to the first argument (the number of columns of your table) and the values in the second argument (empty list, so a table with 0 rows). By using #table this way, it creates columns with default column names Column1, Column2, etcetera. So these column names can be used to sort the columns in your table.

 

[Dan Bliss]

Marcel,
Thanks for coming up with an automated solution.

A breakdown of your nested commands shows the development at each step:
(See also MS Table.ReorderColumns() function M language reference webpage here.

To set up the problem, I've reversed the ColumnName Order to what is wanted.

let


example2 = Table.ReorderColumns(Table.FromRecords({
    [Column3=1, Column2 = "Bob", Column1 = "123-4567"]  
  
}), {"Column11", "Column10","Column9", "Column8", "Column7", "Column6", "Column5", "Column4" }, MissingField.UseNull),


aCount = Table.ColumnCount(example2),
aTable = #table(aCount,{}),
aList = Table.ColumnNames(aTable),
MyTableReorderedByListofColumns = Table.ReorderColumns(example2,aList)
  
in
    MyTableReorderedByListofColumns

Thanks for highlighting the #table(integer, {}) format. The first parameter is the number of columns in an empty table. The {} empty brackets means that no columns have names and each of 'ANY" type, (shown in user interface as "ABC123"). Very smart of you to think up this as a solution.

I find it interesting that the aList lists Columns in numerical order Column1, Column2....Column9, Column10, Column11



If aList were loaded into Excel as a table, then resorted AtoZ, the following would be the result:



Here is the final result, showing that the columns are resorted as required"

 

[MarcelBeug]

... The {} empty brackets means that no columns have names ...

Elaborating on a solution provided by someone else (i.c. me) may involve the risk of misinterpretation like the one quoted here:
the empty braces mean that the table is empty, not that no columns have names.

 

[Dan Bliss]

Yes, I agree {} produces an empty table. What I meant to point out was that since the table has columns with no defined names, Power Query generates columns using a default naming convention: "Column1", "Column2", etc.. which is a very cool thing.