Refresh error: The xxxx column does not exist in the rowset

[V-lianl-msft]

Issue Description:

Scenario1: When you modify the data structure in the data source, the refresh error occurs.
Scenario2: When you have pivot columns steps in query editor, the value in the data source has been modified, the refresh error occurs.

Repro Steps:
Scenario1:
1. Create a sample data in sql server:

CREATE TABLE test (

Category VARCHAR(100),
Sales MONEY,
) ;

INSERT INTO test VALUES('Accessories',9924.60);
INSERT INTO test VALUES('Digital video recorder',10400.00);
INSERT INTO test VALUES('Digital',7234.50);
INSERT INTO test VALUES('Digital SLR',18530.00);

alter table test add qty int;
update test set qty = 10;

2. Create a report in power bi desktop from the sql server and publish it to the power bi service. Configure the Refresh for it .
3. Delete the qty column in sql server: alter table test drop column qty;
4. Try the On Demand Refresh in power bi service, the error rises:

Scenario2:
1. Use the same table above with qty column removed:
2. Pivot columns in query editor:

3. Create the report and publish it to the power bi service. Configure the Refresh for it .
4. Update values in data source(replace digital cell with digital11): update test set Category = 'digital11' where Sales = 7234.50;

5. Try the On Demand Refresh in power bi service, the error rises:

Solution:
1. Download the pbix file for the report.

2. Click the Refresh button on the power bi desktop to get new structure of data source:

If you have any steps that reference the column name in your m-query. You need to manually change it(power query will not automatically change column name when you change the column in your data source). Or you can use the table.columnname function :

For example, change type step , we should use

#"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{Table.ColumnNames(#"Pivoted Column"){0}, type number}, {Table.ColumnNames(#"Pivoted Column"){1}, type number}, {Table.ColumnNames(#"Pivoted Column"){2} ,type number}, {Table.ColumnNames(#"Pivoted Column"){3}, type number}})

Instead of

#"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Accessories", type number}, {"Digital SLR", type number}, {"Digital video recorder", type number}, {"digital11", type number}})

3. Re-publish it again.

Author: Dedmon Dai

Reviewer: Ula Huang, Kerry Wang