Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JanikSchaefer
Helper II
Helper II

Organize my data in Power Query or Power BI Service? Power Bi Pro's, please help me :)

Hey Power BI Community!

I have a question regarding organizing my data, and I really don't find any source where it is explained well:



I have different data sources that all come from sharepoint, mainly excel files (I know, not the best source, we're working on it 😉 )

So let's say I have source 1, source 2 , source 3 and source 2.5 which is an appended query 2&3.

In the end I want to make different reports let's call it Strawberry Report (Source 1 & Source 3), Blueberry Report (Source 1,2 & 3) and Pinapple Report (Source 2.5). They all have to be shared with certain collegues. Also, all of them only use parts of the data of the sources (thus I could maybe make the data much smaller by Power-querying all the unneeded stuff away)

 


Right now, I am getting ALL the data in one pbix file, with 4 different queries.

I also have ALL my visualizations (reports) in one pbix file.
My goal is, to tidy up, make it more sorted.


Now the question:

1) Is it better practice to make a different pbix files and thus datasets for every report, so for example Strawberry report only queries Source 1&3, upload this report solely, updated daily, and share it with my collegues?

Or:

2) Get all the queries and slides in one pbix file that is updated daily, upload it as one "Fruit Salad Report", copy the "Fruit Salad Report" three times and throw all the unwanted Fruit out in Power BI Service, creating the strawberry salads etc which all are based on the same Power BI Dataset?

Or? 3) Is it better to make one pbix file which queries all, and then load this dataset into other pbix files, throw out unwanted data in "fields", visualize it and update every file individually?


Long term goal is to get all the data in a Microsoft ERP.

Hope I didn't make you hungry, thanks for your help guys! 🙂

Cheers,


Janik

6 REPLIES 6
Mariusz
Community Champion
Community Champion

Hi @JanikSchaefer 

 

You can use dataflows Dataflows to create one data source "Fruit salad"
https://docs.microsoft.com/en-us/power-bi/service-dataflows-overview

 

 Later you can create a desktop file / report / dashboard with RLS, so the users are restricted to what they can see.
https://docs.microsoft.com/en-us/power-bi/service-dataflows-overview

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Thanks @Mariusz,

 

this makes sense, I tried to organize my data with data flows, but it seems that I need a Premium Licence (I need linked Entities) which is far too expensive, this brings me back to Datasets, right?

So the same question arises again, which version is the best?

Hi @JanikSchaefer 

 

You can reference the tables as long as you switch off Enable Load on the tables that you need to reference. 

Why do you need to link entities? 

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

@Mariuszthanks for your reply again.


When I load the dataflow I created in Power BI Desktop, it says "Empty Table" in the Power Query Editor.

I need to link entities because I am merging two tables from two different sources.

 

Dataflows doesnt seem to work, could you answer my first question again without dataflows ? 🙂

Hi @JanikSchaefer 

 

Sure, all you need to do is create one dataset with RLS as per the links in my original post.

 

Just to be clear, as mentioned in my last post you do not need premium to link entities as long as the linked entity is not loaded.

You can import individual table ( or get data from Folder if all files in the same folder ), switch off Enable Load on all these files, next Append Queries as New and keep Enable Load only on this Query.

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.