Error when refreshing dataset based on a dataflow entity with a removed column

[Daniil]

I am frequently running into the same issue. Here are the steps I am taking:

1. Create an entity A in a dataflow
2. Connect to the entity A from Power BI Desktop without performing any transformations in Power Query Editor inside Power BI Desktop
3. Publish the dataset to Power BI service. Refresh from Power BI service works at this stage
4. Remove an unused column from the entity A in the dataflow
5. Attempt refresh again in Power BI service. The dataset refresh fails with the following error:
   

To resolve the issue, I do a refresh in Power BI Desktop and re-publish the dataset. Power BI service refresh then works again. Note that I'm not changing any of my queries or anything else -- I simply click the Refresh button.

Surely this is a bug -- I can't see how this is by design.

Important note: I am not applying any transformations to the removed column in Power Query Editor inside Power BI Desktop.

P. S. There should be a separate label for dataflows.

[v-qiuyu-msft]

Hi @Daniil ,

I have reported this issue internally: CRI 105202738. Will update here once I get information. 

Best Regards,
Qiuyun Yu 

[Yechiel]

Hi @Daniil ,

I am trying to reproduce the issue you described:

1. I created a dataflow with entity A

2. Refreshed it.

3. From PowerBI Desktop I connected to the dataflow and selected the entity

4. In the report I only checked 2 of the columns (out of 3)

5. Published and viewed in PowerBI

6. Next I deleted one of the columns that was not in use

7. Viewed report - ok

8. Refreshed dataset - ok

9. Viewed report - ok

10. Refreshed dataflow - ok

11. Viewed report - ok

12. Refreshed dataset - ok

13. Viewed report - ok

Can you please recreate steps? Maybe there is a step I am missing.

Thanks,

Yechiel Bardov

[Daniil]

@Yechiel, unfortunately, it is not clear to me where you removed one of the columns (step 6) and where  you refresh the dataset (steps 8, 10 and 12). Was it in Power BI service or Power BI Desktop? It's crucial. Also, there's no need to view the report (steps 7, 9, 11, and 13).

Anyway, I've just successfully reproduced the bug, and here are the exact steps I took:

1. Create a new dataflow with one entity with the following M script:
   

   let
     Source = {1..10},
     ToTable = Table.FromList(Source, Splitter.SplitByNothing(), type table [Column1 = Int64.Type]),
     Column2 = Table.AddColumn(ToTable, "Keep This", each [Column1] * 2, Int64.Type),
     Column3 = Table.AddColumn(Column2, "Remove later", each [Column1] * 3, Int64.Type)
   in
     Column3

2. Connect to this entity from Power BI Desktop. Do not perform any transformations here.
3. Publish the report to Power BI service.
4. Refresh the dataset in Power BI service. You can immediately do it without a gateway, because the only data source is a dataflow. This will work.
5. Go back to the dataflow in Power BI service and remove one of the columns. In my case, the code became the following:
   

   let
     Source = {1..10},
     ToTable = Table.FromList(Source, Splitter.SplitByNothing(), type table [Column1 = Int64.Type]),
     Column2 = Table.AddColumn(ToTable, "Keep This", each [Column1] * 2, Int64.Type),
     Column3 = Table.AddColumn(Column2, "Remove later", each [Column1] * 3, Int64.Type),
     RemovedColumn = Table.RemoveColumns(Column3, {"Remove later"})
   in
     RemovedColumn

6. Try to refresh the dataset in Power BI service -- you will get the following error:
   

Just for clarity -- here is the M script of the only query in my .pbix file:

let
    Source = PowerBI.Dataflows(null),
    #"b185fb64-3f64-42c0-a508-f0903c0e9df3" = Source{[workspaceId="b185fb64-3f64-42c0-a508-f0903c0e9df3"]}[Data],
    #"28a80b55-96ed-45ee-b7d6-fbdc8a804d12" = #"b185fb64-3f64-42c0-a508-f0903c0e9df3"{[dataflowId="28a80b55-96ed-45ee-b7d6-fbdc8a804d12"]}[Data],
    TestQuery1 = #"28a80b55-96ed-45ee-b7d6-fbdc8a804d12"{[entity="TestQuery"]}[Data]
in
    TestQuery1

As you can see from the code above, I am not touching the [Remove later] column in Power BI Desktop at all, so Power BI service must not complain that it doesn't exist in the rowset. If I refresh the dataset in Power BI Desktop, there is no error, and I can re-publish the report and refresh it without any errors.

[Yechiel]

Hi @Daniil 

Thank you for your detailed mail.

I will test it out and let you know once I have a solution.

Regards,

Yechiel

[Yechiel]

Hi @Daniil ,

Managed to reproduce the error as you described, however I also managed reproducing it by replacing the dataflow with a connection to an SQL database.

Here is the list of columns from my sql table (Only selected City):

Uploaded pbix and refreshed in PowerBI successfully.

The I removed from the SQL table the "ModifiedDate" column.

Refreshed dataset in PowerBI again and got this error:

Modifiying the structure of a data source will cause this problem.

Yechiel

[Daniil]

@Yechiel, yes, you are right -- the issue is not specific to dataflows.

I can't see how this can be by design though because the behavior is inconsistent between Power BI Desktop and Power BI service, so this must be a bug.

[Yechiel]

Hi @Daniil 

I will pass this issue on to the dataset developers.

Regards,

Yechiel

[v-qiuyu-msft]

Hi @Daniil ,

I got below information: 

This is by design. The customer needs to republish if they are editing the model.

Best Regards,
Qiuyun Yu 

[Daniil]

@v-qiuyu-msft if it's by design, then why do we not get an error when a column is added? If removing a column is "editing the model", then adding a column is also "editing the model". The behavior is inconsistent.