Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

V-lianl-msft
Community Support
‎05-10-2021 12:25 AM

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

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;

V-lianl-msft_0-1620631105815.png

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:

V-lianl-msft_1-1620631158072.png


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

V-lianl-msft_2-1620631182589.png

V-lianl-msft_3-1620631197212.png

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;

V-lianl-msft_4-1620631210888.png

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

V-lianl-msft_5-1620631232575.png


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

V-lianl-msft_6-1620631251838.png

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

V-lianl-msft_7-1620631265295.png

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

0