cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PAPutzback2
Helper II
Helper II

How do I connect a Power BI report to an existing Power BI dataset that uses Excel as a datasource?

I have an Excel file with a list of Employee email addresses and some other attributes. I want to use that data in other reports for setting up row level security. When I open up an existing report and go to "get data" and choose Power BI datasets, I don't see my dataset. However, if I open a new report and go to select a Power BI dataset, I can see my Employee List dataset? 

 

The preview feature to use Power BI datasets is on in the report. It must be because it shows me the Power BI Dataset connector.

Thoughts?

Thanks,

Phil

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @PAPutzback2 

 

In your scenario, it has hit some limitations of the preview feature, so you couldn't see the dataset when you tried to add it to an existing report. When you connect to a dataset in an existing report, it will be in DirectQuery mode. Please read the following limitations highlighted in red. 

 

There are also a few limitations you need to keep in mind:

 

However, when you open a new report and go to select a Power BI dataset, by default it will connect to the dataset in Live Connection mode as it is the first data source in the new report, so it will display all Power BI datasets that you have permissions of. That's why you can see the dataset in a new report. 

 

For more limitations about the preview feature, please refer to below official document:

Using DirectQuery for datasets and Azure Analysis Services (preview) - Power BI | Microsoft Docs

 

Additionally, according to the limitation highlighted above in blue, using a table from a dataset to set up row level security in other reports is not supported either. I would suggest creating a Power BI dataflow to connect to the Excel file. Then connect to this dataflow in other reports. You can create a dataflow using define new tables.

Introduction to dataflows and self-service data prep - Power BI | Microsoft Docs

Creating a dataflow - Power BI | Microsoft Docs

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

Hi @PAPutzback2 

 

In your scenario, it has hit some limitations of the preview feature, so you couldn't see the dataset when you tried to add it to an existing report. When you connect to a dataset in an existing report, it will be in DirectQuery mode. Please read the following limitations highlighted in red. 

 

There are also a few limitations you need to keep in mind:

 

However, when you open a new report and go to select a Power BI dataset, by default it will connect to the dataset in Live Connection mode as it is the first data source in the new report, so it will display all Power BI datasets that you have permissions of. That's why you can see the dataset in a new report. 

 

For more limitations about the preview feature, please refer to below official document:

Using DirectQuery for datasets and Azure Analysis Services (preview) - Power BI | Microsoft Docs

 

Additionally, according to the limitation highlighted above in blue, using a table from a dataset to set up row level security in other reports is not supported either. I would suggest creating a Power BI dataflow to connect to the Excel file. Then connect to this dataflow in other reports. You can create a dataflow using define new tables.

Introduction to dataflows and self-service data prep - Power BI | Microsoft Docs

Creating a dataflow - Power BI | Microsoft Docs

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

PAPutzback2
Helper II
Helper II

The problem is my dataset does not appear in the list of available datasets to choose from. 

jennratten
Super User
Super User

Hello - this article should help you.  It discusses composite data models with live connections to Power BI datasets.  

https://sqlserverbi.blog/2021/01/10/a-first-look-at-gen-2-composite-models-with-live-power-bi-datase... 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors