none
How to I Sort or Changing Default Column Names? (Column1, Column2...) RRS feed

  • Question

  • I have a large "pivoted" data set with 8 columns of data then 3,000+ columns with date values as the header. One of the required field names is a date in the name of each file being imported.  After managing the data down to the "DataDate" field (from the file name) and the Data from each file (xlsx files) and I expand the data I get a list of Columns:  Column1, Column2, ... Column10, Column11, ... Column100, etc.  The 1st 8 columns contain data that I need to use in the "Unpivot Other Column" step, but when I promote headers, Power Query sorts the columns by the column header name, so I end up with Column1, Column100, column101, ... Column99 which is all the 235 columns that can be loaded in the view.  All the rest of my columns are in Column2, Column3, etc, so they don't load in the preview. (I need column1, column2, column3, etc.)

    How do I load more columns in the preview?  Or, can I control the default column names to "Column00001, Column0002, ... Column03569?, so when I promote headers, they remain in the original order of the file? Or, can I tell Power Query somewhere "Do not sort columns on import"?

    Thanks!


    • Edited by jbt_PwrPvt Saturday, December 3, 2016 8:50 PM
    Saturday, December 3, 2016 8:47 PM

Answers

  • Unfortunately I cannot replicate the behaviour you've described, but there is a fairly easy step to solve it.

    Just take the first 8 columns that you see -> unpivot others -> edit the code, which should look sth like:

    Table.UnpivotOtherColumns(#"Promoted Headers", {"1", "10", "110", "111", "112", "14", "15", "16", "9", "10_1"}, "Attribute", "Value")

    To:

    Table.UnpivotOtherColumns(#"Promoted Headers", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}, "Attribute", "Value")

    So "just" replace the names that you've fetched with the ones you actually need.


    Imke Feldmann TheBIccountant.com

    • Marked as answer by jbt_PwrPvt Sunday, January 15, 2017 3:48 PM
    Monday, December 5, 2016 10:15 AM
    Moderator
  • The scenario is not clear to me either, but anyhow I created the function below that can be used to rename columns as indicated.

        fnRenameColumns = (TableSoFar as table, NtoKeep as number, NDigits as number) as table =>
        let
            CurrentAndNewColumnNames = Table.FromColumns({Table.ColumnNames(TableSoFar),
                                                           
     List.Combine({List.FirstN(Table.ColumnNames(TableSoFar),NtoKeep),
                                                                         
     List.Transform({1..Table.ColumnCount(TableSoFar)-NtoKeep}, each "Column"&Text.PadStart(Text.From(_),NDigits,"0"))
                                                                        
     })
                                                        
     }),
            RenamedColumns = Table.RenameColumns(TableSoFar,Table.ToRows(CurrentAndNewColumnNames))
        in
            RenamedColumns

    The function takes as arguments the orginal table, the number of column names to keep and the number of digits in the new column names, so in the example provided, the function would be called like:

    TableRenamedColumns = fnRenameColumns(CurrentTable, 8, 5)



    • Edited by MarcelBeug Tuesday, December 6, 2016 5:22 AM
    • Marked as answer by jbt_PwrPvt Sunday, January 15, 2017 3:58 PM
    Tuesday, December 6, 2016 5:21 AM
  • Hi John. We've recently added a feature that allows you to sort the columns you're expanding alphabetically or in their original order. Hopefully this will provide an easy way for you to get the sorting you're after.

    If you don't see this feature yet, look for it in an upcoming release.

    Ehren

    Tuesday, January 24, 2017 6:49 PM
    Owner

All replies

  • Unfortunately I cannot replicate the behaviour you've described, but there is a fairly easy step to solve it.

    Just take the first 8 columns that you see -> unpivot others -> edit the code, which should look sth like:

    Table.UnpivotOtherColumns(#"Promoted Headers", {"1", "10", "110", "111", "112", "14", "15", "16", "9", "10_1"}, "Attribute", "Value")

    To:

    Table.UnpivotOtherColumns(#"Promoted Headers", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}, "Attribute", "Value")

    So "just" replace the names that you've fetched with the ones you actually need.


    Imke Feldmann TheBIccountant.com

    • Marked as answer by jbt_PwrPvt Sunday, January 15, 2017 3:48 PM
    Monday, December 5, 2016 10:15 AM
    Moderator
  • The scenario is not clear to me either, but anyhow I created the function below that can be used to rename columns as indicated.

        fnRenameColumns = (TableSoFar as table, NtoKeep as number, NDigits as number) as table =>
        let
            CurrentAndNewColumnNames = Table.FromColumns({Table.ColumnNames(TableSoFar),
                                                           
     List.Combine({List.FirstN(Table.ColumnNames(TableSoFar),NtoKeep),
                                                                         
     List.Transform({1..Table.ColumnCount(TableSoFar)-NtoKeep}, each "Column"&Text.PadStart(Text.From(_),NDigits,"0"))
                                                                        
     })
                                                        
     }),
            RenamedColumns = Table.RenameColumns(TableSoFar,Table.ToRows(CurrentAndNewColumnNames))
        in
            RenamedColumns

    The function takes as arguments the orginal table, the number of column names to keep and the number of digits in the new column names, so in the example provided, the function would be called like:

    TableRenamedColumns = fnRenameColumns(CurrentTable, 8, 5)



    • Edited by MarcelBeug Tuesday, December 6, 2016 5:22 AM
    • Marked as answer by jbt_PwrPvt Sunday, January 15, 2017 3:58 PM
    Tuesday, December 6, 2016 5:21 AM
  • Hi jbt_PwrPvt. Is the alphabetical column ordering happening with the Promote Headers step or the Expand step (or somewhere else)? Promote Headers shouldn't change the order of the columns, but Expand can (in some situations) order the columns alphabetically. Can you clarify?

    Thanks,

    Ehren

    Friday, December 9, 2016 6:53 PM
    Owner
  • Hi jbt_PwrPvt. Is the alphabetical column ordering happening with the Promote Headers step or the Expand step (or somewhere else)? Promote Headers shouldn't change the order of the columns, but Expand can (in some situations) order the columns alphabetically. Can you clarify?

    Thanks,

    Ehren

    Hi jbt_PwrPvt,

    Are you able to answer Ehren's questions?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Thursday, December 22, 2016 10:10 AM
    Moderator
  • Sorry to all - i was asleep at the wheel...

    The "sorting" was happening BEFORE I promoted headers.  Power Query assigns default column names to the columns of data and names them "Column1, Column2, ..."  AND sorts the columns in order.  When you have 3000+ columns of data to deal with,

     ExpandedExcelData = Table.ExpandTableColumn(FilterOutParameterDataDates, "Data", {"Column1", "Column10", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109", "Column11", "Column110", "Column111", "Column112", "Column113", "Column114", etc...

    BEFORE you promote headers, the columns are all mixed up and of the first 8 columns that I need, only the first few are loaded.  I need column1, column2... column8.  but only column1 and column2 are loaded in the preview because power query sorts by column name when default columns are assigned.

    What I found out is I can unpivot other columns WITHOUT getting everything in order by editing the code:

        UnpivotFields_TimeLineDate = Table.UnpivotOtherColumns(RenameField_Column1_to_DataDate, {"Activity ID", "Activity Name", "Resource ID Name", "Start", "Finish", "DataDate", "Spreadsheet Field"}, "Attribute", "Value")

    I can do this because I know what the column names are even if they are not loaded into the preview.

    A big improvement to power query would be to count the columns and ensure when assigning default names, they are padded with zeros so when power query sorts the columns, they remain in native order.


    John Thomas

    Sunday, January 15, 2017 3:46 PM
  • That's correct.  It's happening in the Expand step.

        ExpandedExcelData = Table.ExpandTableColumn(FilterOutParameterDataDates, "Data", {"Column1", "Column10", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109", "Column11", "Column110", "Column111", "Column112", "Column113", "Column114", "Column115", "Column116", "Column117", "Column118", "Column119", "Column12", "Column120", etc...

    Notice the lack of columns, 2, 3, 4, 5, 6, 7, 8, 9?  They are way down the line, not even loaded into the preview.  I discovered you can work with the columns even if you don't have them loaded.  As long as you know the field names you can edit the M code to reference them.  See above reply to Ehren.


    John Thomas

    Sunday, January 15, 2017 3:55 PM
  • Hi John. We've recently added a feature that allows you to sort the columns you're expanding alphabetically or in their original order. Hopefully this will provide an easy way for you to get the sorting you're after.

    If you don't see this feature yet, look for it in an upcoming release.

    Ehren

    Tuesday, January 24, 2017 6:49 PM
    Owner