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

[Anonymous]

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

[amitchandak]

Refer:https://docs.microsoft.com/en-us/power-bi/guidance/

https://community.powerbi.com/t5/MBAS-Gallery/bd-p/MBAS_Gallery

[Mariusz]

Hi @Anonymous 

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

[Anonymous]

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?

[Mariusz]

Hi @Anonymous 

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

[Anonymous]

@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 ? 🙂

[Mariusz]

Hi @Anonymous 

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