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
Community Support