2 Replace Statements on Same Column results in an Error when transforming Data

[Anonymous]

I've experienced a new and unexpected error on exisitng Power BI Data Sets when having multiple replace transformation statements on the same column. The same code does not result in an error in MS Excel Power Query. The lines in the code that fail:

#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [ITEMNMBR] <> null and [ITEMNMBR] <> ""),

    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","","Not Assigned",Replacer.ReplaceValue,{"Customer", "category"}),

    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"Not Assigned",Replacer.ReplaceValue,{"Customer", "category"})

in

    #"Replaced Value1"

   The error:

Failed to save modification to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Invalid column name 't0_1'..'.

If I delete one of the replace value lines, the transformation processes corrrectly but leaves me with an unresolved replacement.

This is a new error on existing datasets..  It doesn't matter if the multiple replacements are on one or more columns. 

I've been able to work around the error by adding custom columns. This seem unncessary.  Has anyone else seen this error with the same conditions.  The transformation was done on Power BI Desktop Version: 2.80.5803.1061 64-bit (April 2020)

Thanks,

Keith

[v-yuta-msft]

@Anonymous ,

I could not reproduce this issue on my side. From the error message, it seems this issue is caused by unrecognized column names, have you checked the column names? For example, check if there's any unprintable characters in the column names.

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft 

Hi Jimmy,

Thanks for the feedback.  The error message is misleading. There was column issues. Instead with furter review and testing we found the following:

This is new.

Existing and working queries / data sets with a Sort followed by multiple replace throw the error.  The error occurs in the Power BI Desktop when refreshing.   The same queries / data sets in the Service appear to refresh without this error.  It is concerning that prior working code no longer works, that a Sort on an unrelated column in a certain sequence to multiple Replace steps on other columns works in one sequence but not the other.  The impact is a rewrite and test of existing queries / data sets that have this condition once discovered.

The error is relatively recent, first occurrence seen yesterday on previously working Data Sets / Reports in the Power BI Desktop. The last time that I can pinpoint a refresh that worked  on the original code was on 4/28. 

Here are the 3 tested scenarios.

Regards,

Keith Paul

It appears that it is a combination and sequence of the Sort and Replace commands where the  Sort precedes the multiple Replace.


let

    Source = Sql.Database("xxxxxxxxxx", "xxxxx"),

    dbo_IV00101_Extended = Source{[Schema="dbo",Item="IV00101_Extended"]}[Data],

    #"Removed Other Columns" = Table.SelectColumns(dbo_IV00101_Extended,{"ITEMNMBR", "AgileCustomer", "category"}),

    #"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns",{{"ITEMNMBR", Text.Trim, type text}, {"AgileCustomer", Text.Trim, type text}, {"category", Text.Trim, type text}}),

    #"Sorted Rows" = Table.Sort(#"Trimmed Text",{{"ITEMNMBR", Order.Ascending}}),

    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",null,"Not Assigned",Replacer.ReplaceValue,{"AgileCustomer", "category"}),

    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","","Not Assigned",Replacer.ReplaceValue,{"AgileCustomer", "category"})

in

    #"Replaced Value1"



A filter then single replace works.


let

    Source = Sql.Database("xxxxxxxxxx", "xxxxx"),

    dbo_IV00101_Extended = Source{[Schema="dbo",Item="IV00101_Extended"]}[Data],

    #"Removed Other Columns" = Table.SelectColumns(dbo_IV00101_Extended,{"ITEMNMBR", "AgileCustomer", "category"}),

    #"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns",{{"ITEMNMBR", Text.Trim, type text}, {"AgileCustomer", Text.Trim, type text}, {"category", Text.Trim, type text}}),

    #"Sorted Rows" = Table.Sort(#"Trimmed Text",{{"ITEMNMBR", Order.Ascending}}),

    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",null,"Not Assigned",Replacer.ReplaceValue,{"AgileCustomer", "category"})

in

    #"Replaced Value"



Moving the filter to below the multiple replace works.


let

    Source = Sql.Database("xxxxxxxxxx", "xxxxx"),

    dbo_IV00101_Extended = Source{[Schema="dbo",Item="IV00101_Extended"]}[Data],

    #"Removed Other Columns" = Table.SelectColumns(dbo_IV00101_Extended,{"ITEMNMBR", "AgileCustomer", "category"}),

    #"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns",{{"ITEMNMBR", Text.Trim, type text}, {"AgileCustomer", Text.Trim, type text}, {"category", Text.Trim, type text}}),

    #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text",null,"Not Assigned",Replacer.ReplaceValue,{"AgileCustomer", "category"}),

    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","","Not Assigned",Replacer.ReplaceValue,{"AgileCustomer", "category"}),

    #"Sorted Rows" = Table.Sort(#"Replaced Value1",{{"ITEMNMBR", Order.Ascending}})

in

    #"Sorted Rows"

[USQDavidGHarvey]

I received the same error after adding a sort step before a series of value replacements.

My scenario included the removal of the sort column immediately after the sort step.  Therefore, I solved it by keeping the sort column in the dataset until the last step. (Performing the removal of the sort column in the final step, which also happens to be outside of query folding.)

My understanding of the background facts:

I assume the error occurs because of query folding.  The resulting SQL is perhaps performing the ORDER BY statement on a subquery or CTE that's already restricted the columns to return.  Because the sort column no longer exists, the ORDER BY fails.  Also, the presence of the complex Table.ReplaceValue steps might be what's causing the query folding to use a subquery or CTE.  

If you try performing the removal of the sort column at the last step and still get the error, you should try using the Table.StopFolding command before removing it. Table.StopFolding - PowerQuery M | Microsoft Docs