PDA

View Full Version : Keep order of columns when expanding



omezquita
2017-01-05, 07:02 PM
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!

DeadElle
2017-01-05, 07:27 PM
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

omezquita
2017-01-05, 08:34 PM
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: 6199, Power Query use the function
Table.ExpandTableColumn and writes the column names in alphabetical order rather than in the original order of the columns.

DeadElle
2017-01-05, 08:43 PM
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: 6199, Power Query use the function
Table.ExpandTableColumn and writes the column names in alphabetical order rather than in the original order of the columns.

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).

omezquita
2017-01-06, 12:19 AM
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:



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,

DeadElle
2017-01-06, 06:52 AM
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