Keep order of columns when expanding

O

omezquita

New member
Joined
Apr 1, 2016
Messages
19
Reaction score
0
Points
0
Website
www.masterdataanalysis.com
Excel Version(s)
2016
Hi,

When I click on the Expand Button to expand the columns of a table, PQ expands the columns in alphabetical order.

Is there a way to get the columns in the original order?

Thanks!
 
1) check query steps for any ascending/descending filter applied to the column

2) check the mere table if there is any filter applied to the heading
 
Thanks, I'm not referring to the order within the columns, I'm referring to the order of the column names in the table.

When I click this button: expand button.PNG, Power Query use the function
Code: 
Table.ExpandTableColumn
and writes the column names in alphabetical order rather than in the original order of the columns.
 
omezquita said:
Thanks, I'm not referring to the order within the columns, I'm referring to the order of the column names in the table.

When I click this button: View attachment 6199, Power Query use the function
Code: 
Table.ExpandTableColumn
and writes the column names in alphabetical order rather than in the original order of the columns.
Click to expand...

when you use the expand button, in the list you should see the headers in the same order that the original source is and as the order that should appear in the table.

btw, you can use a workaround selecting each single column and dragging it to your desired position (Table.ReorderColumns function).
 
Actually is not happening in that way. When I import from a single file it does show the columns in the same order, however, in this case I'm importing all files from a folder.

This is the M code just before clicking on the expand button:

Code: 
let    FolderPath = fxGetValFromTable("RA_Table", "Folder"),
    Source = Folder.Files(FolderPath),
    #"Filename and Extension in Lowercase" = Table.TransformColumns(Source,{{"Name", Text.Lower}, {"Extension", Text.Lower}}),
    #"Filter .xlsx files" = Table.SelectRows(#"Filename and Extension in Lowercase", each ([Extension] = ".xlsx")),
    #"Filter files with ""commresults"" in name" = Table.SelectRows(#"Filter .xlsx files", each Text.Contains([Name], "commresults")),
    #"Remove Unnecesary Columns" = Table.SelectColumns(#"Filter files with ""commresults"" in name",{"Content", "Name"}),
    #"Get contents from Excel Files" = Table.AddColumn(#"Remove Unnecesary Columns", "Contents", each Excel.Workbook([Content], true)),
    #"Expand Contents Column" = Table.ExpandTableColumn(#"Get contents from Excel Files", "Contents", {"Data", "Hidden", "Item", "Kind", "Name"}, {"Data", "Hidden", "Item", "Kind", "Name.1"}),
    #"Filter Sheets" = Table.SelectRows(#"Expand Contents Column", each [Kind] = "Sheet"),
    #"Keep sheets with date pattern only MMMYY" = Table.SelectRows(#"Filter Sheets", each (try Date.FromText([Item]) otherwise "Remove") <> "Remove"),
    #"Removed Other Columns" = Table.SelectColumns(#"Keep sheets with date pattern only MMMYY",{"Name", "Data"}),

You just gave me a great idea with the Table.ReorderColumns functions. I won't do it manually since there are lots of columns but I can create a list with the original order and pass it to the Reorder function.

I wonder if there's a way of avoiding this rather than changing the order afterwards.

Regards,
 
As far I can see from your M code, you're importing data from multiple .xlsx files and that's why columns are sorted alphabetically.

If you want to sort data in your desired way, you have to change your code to append every single file sequencially, and it might not be a convenient way in your case.

As my M knowledge, Table.ReorderColumns will do the trick for you, if there are other ways to do it maybe someone else will give you another hint :)

Regards
 
You must log in or register to reply here.
Back
Top