none
Error trying to use variable for the field name in a SelectRows line RRS feed

  • Question

  • I have the following Excel 2016 Power Query (Get & Transform query). It works fine, but I'm trying to use variables for the field names in the two SelectRows lines:

        let

            //Variables:
                //Server Name:
                    DefnameServerName = Excel.CurrentWorkbook(){[Name="IMPORT_SERVER_NAME"]}[Content],
                    ServerName = DefnameServerName{0}[Column1],
                //Database Name:
                    DefnameDatabaseName = Excel.CurrentWorkbook(){[Name="IMPORT_DATABASE_NAME"]}[Content],
                    DatabaseName = DefnameDatabaseName{0}[Column1],
                //Schema Name:
                    DefnameSchemaName = Excel.CurrentWorkbook(){[Name="IMPORT_SCHEMA_NAME"]}[Content],
                    SchemaName = DefnameSchemaName{0}[Column1],
                //Table Name:
                    DefnameTableName = Excel.CurrentWorkbook(){[Name="IMPORT_TABLE_NAME"]}[Content],
                    TableName = DefnameTableName{0}[Column1],
                //ID Field Name:
                    DefnameFieldNameID = Excel.CurrentWorkbook(){[Name="IMPORT_FIELDNAME_ID"]}[Content],
                    FieldNameID = DefnameFieldNameID{0}[Column1],
                //Customer Field Name:
                    DefnameFieldNameCustomer = Excel.CurrentWorkbook(){[Name="IMPORT_FIELDNAME_CUSTOMER"]}[Content],
                    FieldNameCustomer = DefnameFieldNameCustomer{0}[Column1],
                //Region Field Name:
                    DefnameFieldNameRegion = Excel.CurrentWorkbook(){[Name="IMPORT_FIELDNAME_REGION"]}[Content],
                    FieldNameRegion = DefnameFieldNameRegion{0}[Column1],
                //Month Field Name:
                    DefnameFieldNameMonth = Excel.CurrentWorkbook(){[Name="IMPORT_FIELDNAME_MONTH"]}[Content],
                    FieldNameMonth = DefnameFieldNameMonth{0}[Column1],
                //Forecast Value Field Name:
                    DefnameFieldNameForecastValue = Excel.CurrentWorkbook(){[Name="IMPORT_FIELDNAME_FORECAST_VALUE"]}[Content],
                    FieldNameForecastValue = DefnameFieldNameForecastValue{0}[Column1],

            //Parameters:
                //ID:
                    DefnameParameterID = Excel.CurrentWorkbook(){[Name="IMPORT_PARAMETER_ID"]}[Content],
                    ParameterID = DefnameParameterID{0}[Column1],
                //Customer:
                    DefnameParameterCustomer = Excel.CurrentWorkbook(){[Name="IMPORT_PARAMETER_CUSTOMER"]}[Content],
                    ParameterCustomer = DefnameParameterCustomer{0}[Column1],

            Source = Sql.Database(ServerName, DatabaseName),
            dbo_MidtermOmnibusForecast = Source{[Schema=SchemaName,Item=TableName]}[Data],
            RemoveOtherColumns = Table.SelectColumns(dbo_MidtermOmnibusForecast,{FieldNameID, FieldNameCustomer, FieldNameMonth, FieldNameForecastValue, FieldNameRegion}),
            FilterByUUID = Table.SelectRows(RemoveOtherColumns, each ([UUID] = ParameterID)),
            FilterByCustomer = Table.SelectRows(FilterByUUID, each ([ForecastName] = ParameterCustomer)),
            PivotMonths = Table.Pivot(Table.TransformColumnTypes(FilterByCustomer, {{FieldNameMonth, type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(FilterByCustomer, {{FieldNameMonth, type text}}, "en-US")[ForecastDate]), FieldNameMonth, FieldNameForecastValue, List.Sum),
            SortByCustomerThenRegion = Table.Sort(PivotMonths,{{FieldNameCustomer, Order.Ascending}, {FieldNameRegion, Order.Ascending}})
        in
            SortByCustomerThenRegion

                   

    I've tried this:

            FilterByUUID = Table.SelectRows(RemoveOtherColumns, each ([FieldNameID] = ParameterID)),
            FilterByCustomer = Table.SelectRows(FilterByUUID, each ([FieldNameCustomer] = ParameterCustomer)),

    And I've tried this:

            FilterByUUID = Table.SelectRows(RemoveOtherColumns, each (FieldNameID = ParameterID)),
            FilterByCustomer = Table.SelectRows(FilterByUUID, each (FieldNameCustomer = ParameterCustomer)),

    But they both break the query. What do I need to do to use the variables for the field names in the two SelectRows lines?
    Thursday, June 29, 2017 4:56 PM

Answers

  • Oh - now I understand.

    You cannot use square brackets with variable field names, but have to use Record.Field instead like this:

    FilterByUUID = Table.SelectRows(RemoveOtherColumns, each (Record.Field(_, FieldNameID) = ParameterID)),


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    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 - Thanks!



    Thursday, June 29, 2017 5:19 PM
    Moderator

All replies

  • From what I can see, your query should work. - At least with regards to the parameters.

    1) What does the error-message say?

    2) Did you try your query with hard-coded values instead?


    Regards,

    Imke Feldmann

    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.TheBIccountant.com , Twitter: @TheBIccountant

    Thursday, June 29, 2017 5:02 PM
    Moderator
  • Which one should work? With the square brackets around the variable, or not?

    Here's the error I get if I replace this:

    FilterByUUID = Table.SelectRows(RemoveOtherColumns, each ([UUID] = ParameterID)),

    with this:

    FilterByUUID = Table.SelectRows(RemoveOtherColumns, each ([FieldNameID] = ParameterID)),

    Error:

    Expression.Error: The field 'FieldNameID' of the record wasn't found.

    If I remove the square brackets around the fieldname variable, like this:

    FilterByUUID = Table.SelectRows(RemoveOtherColumns, each (FieldNameID = ParameterID)),

    I don't get an error, but the table is empty -- no rows. If I use the actual field name (not a variable), then it returns many rows.

    Yes, if I use hard-coded values for the field names instead of variables, it works fine.

    Thursday, June 29, 2017 5:13 PM
  • Oh - now I understand.

    You cannot use square brackets with variable field names, but have to use Record.Field instead like this:

    FilterByUUID = Table.SelectRows(RemoveOtherColumns, each (Record.Field(_, FieldNameID) = ParameterID)),


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    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 - Thanks!



    Thursday, June 29, 2017 5:19 PM
    Moderator
  • Thanks, that works!

    Greg


    • Edited by GJL65 Thursday, June 29, 2017 5:51 PM
    Thursday, June 29, 2017 5:50 PM
  • Great!

    You were correct:

    Your variable returns a text-field and this can be used where the input for the formula expects a text-field or any.

    If you would hardcode the value there instead, you would have to enclose it in " ". So I imagine it that the content of the variable is always enclosed in " ".

    I believe this is the reason why it doesn't work in the square brackets of the record, because it would actually be:

    ["YourVariableContent"].


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    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 - Thanks!

    Thursday, June 29, 2017 6:04 PM
    Moderator
  • Your variable returns a text-field and this can be used where the input for the formula expects a text-field or any.

    If you would hardcode the value there instead, you would have to enclose it in " ". So I imagine it that the content of the variable is always enclosed in " ".

    I believe this is the reason why it doesn't work in the square brackets of the record, because it would actually be:

    ["YourVariableContent"].



    FWIW, that made me wonder if it would work if the variable included the square brackets. But it does not. 
    • Edited by GJL65 Thursday, June 29, 2017 6:28 PM
    Thursday, June 29, 2017 6:28 PM
  • I feel with you, have been in the same position :-)

    Your attempt would make then wrap the square brackets into " ", which actually is an escaping sign:

    FilterByUUID = Table.SelectRows(RemoveOtherColumns, each ("[FieldNameID]" = ParameterID)),


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    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 - Thanks!

    Thursday, June 29, 2017 6:37 PM
    Moderator