none
Lookup column names with Power Query RRS feed

  • Question

  • Hi,

    before I go ahead and change source files, I was wondering if I can use Power Query to get a model with the following data. To simplify the example, I have the following dataset in an Excel sheet:

    In another Excel sheet, the column names are defined, for example:

    What would like to have in Power Query is one table with the column names from the second screenshot in column B. Is there a way to link these two tables together in Power Query in order to use these names as column headers in the dataset?

    Thanks!


    - If a post answers your question, please click "Mark As Answer" on that post!

    Tuesday, April 15, 2014 12:38 PM

Answers

  • You can filter the list of renames against the list of actual column names with something like

        FilteredColumns = Table.SelectRows(Columns, each List.Contains(Table.ColumnNames(Source), [Name])),

    Again, the working end-to-end:

    let
        Source = Table.FromRows({{"New York", 23, 51, 732}, {"Chicago", 25, 421, 23}, {"Los Angeles", 632, 22, 423}}, {"City", "Col 1", "Col 2", "Col 3"}),
        Columns = Table.FromRows({{"Col 1", "Column 1"}, {"Col 2", "Column 2"}, {"Col 3", "Column 3"}, {"Col 4", "Column 4"}}, {"Name", "Value"}),
        FilteredColumns = Table.SelectRows(Columns, each List.Contains(Table.ColumnNames(Source), [Name])),
        ColumnsAsRenames = Table.TransformRows(FilteredColumns, Record.FieldValues),
        RenamedColumns = Table.RenameColumns(Source, ColumnsAsRenames)
    in
        RenamedColumns
    

    Wednesday, April 16, 2014 3:01 PM

All replies

  • I'm not sure how best to do this through the UI, but this is pretty straightforward with the M library. If the first data set is "Source" and the second data set is "Columns", you can say

    = Table.RenameColumns(Source, Table.TransformRows(Columns, Record.FieldValues))

    Here's a slightly more verbose example:

    let
        Source = Table.FromRows({{"New York", 23, 51, 732}, {"Chicago", 25, 421, 23}, {"Los Angeles", 632, 22, 423}}, {"City", "Col 1", "Col 2", "Col 3"}),
        Columns = Table.FromRows({{"Col 1", "Column 1"}, {"Col 2", "Column 2"}, {"Col 3", "Column 3"}}, {"Name", "Value"}),
        ColumnsAsRenames = Table.TransformRows(Columns, Record.FieldValues),
        RenamedColumns = Table.RenameColumns(Source, ColumnsAsRenames)
    in
        RenamedColumns
    

    Tuesday, April 15, 2014 1:38 PM
  • Hi Curt,
    thank you very much for your reply. I tried to implement this with the actual dataset I'm using, it seems that it almost works. It seems that with this construction I can't have more columns in the second table than exist in the first one. For example, in the second screenshot where I define the columns, I add "Col 4". Since this does not exist in the first screenshot, I get an error: 

    Expression.Error: The column 'Col 4' of the table was not found.

    Do you know if there is a way to solve this?

    Thanks!

    Erik


    - If a post answers your question, please click "Mark As Answer" on that post!

    Wednesday, April 16, 2014 7:55 AM
  • You can filter the list of renames against the list of actual column names with something like

        FilteredColumns = Table.SelectRows(Columns, each List.Contains(Table.ColumnNames(Source), [Name])),

    Again, the working end-to-end:

    let
        Source = Table.FromRows({{"New York", 23, 51, 732}, {"Chicago", 25, 421, 23}, {"Los Angeles", 632, 22, 423}}, {"City", "Col 1", "Col 2", "Col 3"}),
        Columns = Table.FromRows({{"Col 1", "Column 1"}, {"Col 2", "Column 2"}, {"Col 3", "Column 3"}, {"Col 4", "Column 4"}}, {"Name", "Value"}),
        FilteredColumns = Table.SelectRows(Columns, each List.Contains(Table.ColumnNames(Source), [Name])),
        ColumnsAsRenames = Table.TransformRows(FilteredColumns, Record.FieldValues),
        RenamedColumns = Table.RenameColumns(Source, ColumnsAsRenames)
    in
        RenamedColumns
    

    Wednesday, April 16, 2014 3:01 PM
  • Hi Guys -

    I'm having the same exact issue; however, my two tables are not in Excel but rather coming from a generic ODBC source.  I pull in the two tables in two separate queries, but when I try to use Table.RenameColumns on them it causes some columns to throw errors.  Below is my full code.  The strange thing is that Columns by itself is good, and Source by itself is also good, but once I introduce Table.RenameColumns the first row of data for only some of the columns becomes blank (not null, simply blank) and some columns throw this error

    (DataSource.Error: ODBC: We cannot convert a value of type System.Int32 to type System.String).

    Again, the rows and columns do not exhibit either behaviors when viewed in Source.  I've spent almost three days on this and cannot figure it out, please help!  Thanks.

    let
        Columns = HeaderList,
        Source= MetricsRaw,
        FilteredColumns = Table.SelectRows(Columns, each List.Contains(Table.ColumnNames(Source), [name])),
        ColumnsAsRenames = Table.TransformRows(FilteredColumns, Record.FieldValues),
        RenamedColumns = Table.RenameColumns(Source, ColumnsAsRenames)
    in
        RenamedColumns

    Monday, September 28, 2015 8:59 PM