Update Dataset to show added columns in the SQL connection

[RossChevalier]

I have two datasets connecting to the same SQL View via a Gateway Connection using the same gateway.  One of them shows all the columns in the SQL View, and the other does not.  How do I update the dataset that does not see all the columns to see them?  There are many reports attached to the dataset that is missing columns and I cannot recreate them all.

Some months back columns were added to the view and I was able to get the dataset to show the additional columns but I have no idea how it was done, and the screen recording of the process did not save properly.

Is there a way to move a report using one dataset to a different dataset, given that all the columns in use are the same?

Thanks kindly!

[v-yingjl · Accepted solution]

Hi @RossChevalier ,
"Is there a way to move a report using one dataset to a different dataset, given that all the columns in use are the same?"

You should know that a report in power bi service is based on a dataset, a workspace in power bi service cannot have the same dataset but can create many reports based on one dataset.

If the datasets have the same dimensions and measures, we can switch a report from one PowerBI dataset to another PowerBI dataset, refer this blog.

In power bi service, we can move a report / dataset from a workspace to another workspace directly, refer this issue.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[RossChevalier]

Thank you kindly for your timely answer.

[GilbertQ]

Hi there

What you could do is to open the PBIX where it is missing the columns.

Then go into Power Query and let the table load from the view. You should then see all the columns.

Click Close and Apply and then upload the PBIX again.

[RossChevalier]

Ok I now know that Power Query is now under Transform but the only option there is to choose the datasource.  No option to view the table or anything like that.  The table appears on the far right as usual but does not show the new columns

[GilbertQ]

Hi there

Ok it appears that you are then connecting to a dataset in the Power BI Service?

I would look at the Power BI report in the service and then click on View Lineage to see what dataset it is connecting to.

Once you find that dataset you will then need to open that dataset to update the columns.

[RossChevalier]

Thank you for your attempt to assist.  I have done what you suggested prior to posting my query and it does not work.

[RossChevalier]

Thanks Gilbert this sounds brilliant but I cannot find Power Query in Power BI Desktop anywhere.  According to the web it should be under Home, but I don't see it there at all.  I don't have an edit queries button anywhere.

What have I done wrong?

Thanks!