Table Query Design in Microsft Access or Power BI Desktop - What is good practice for design?

[Anonymous]

Hi - I am not an IT developer, but have strong MS Access skills and I am able to merge many data sources in MS Access, using relational models and unique record sets. I am also able to utilise PBI desktop through to publishing and so on, so for "simple" tables and outputs this is all working - 

However, I am struggling to perceive/understand the best way to query data in Power BI Desktop. This maybe a knowledge gap or misunderstanding about what PBI calls queries. i.e. my mind is seeing MS Access queries, whese PBI seems to have a water down version of MS Access queries, albeit similar!

My reality is that in MS Access, I can "freely" create joins, group and cross calculate data, at will. I can milestone data between queries and trap certain records during the data flow.  However, in PBI, when I use the "merge query" function, I seem to be limited to setting up 2 relationships at a time to create a new output.

Now don't get me wrong, I have used the data layout and modeling to join several tables and I am satisfied that when tables are joined here, I am able to create a visualisation that is pulling related content into a credible output.

I am stuggling to comprehend the limitation of the PBI query function. The transform, relabelling of columns and simple features are great, as is the merge function etc, but "to me" these are not actually what I would call a query when compared to joining several MS Access tables at once.

Therefore, is it better to use MS Access as a pass through database from the source, create some queries and then link them to a "flat table / single output" in Excel or ideally an O365 list, which then PBI can refresh from? Else it seems quite messy to create, pairs of linked tables in PBI and kind of build up a foundation pyramid of tables to finally reach an output as it were?

Grateful for any feedback, thanks.

[v-yulgu-msft]

Hi @Anonymous ,

Power BI desktop provides Access Database connector.

Best regards,

Yuliana Gu

[Anonymous]

Hi - 

We know about the connector and use it.....I am tyring to gauge what people are doing out there on this big blue marble.....

The "best" and optimised" solution I have seen is that small SP list feed PBI really well, in terms of refresh and the data being dynamic with total hands off once published......

Whereas once you are start involving excel, Access and other sources, the acutal mechancics of how do you get a published refresh working? I like all the measures that can be created, but as a none developer/IT bod, doing equations and wrapping things in the code seems problematic and complex, when compared to say doing a group and count in MS Access. The result in Access can easily be traced through to source for Quality purposes, where as in PBI you have to filter manually and all sorts of fiddling around.....As eluded to, perhaps its just me and that I dont have the right compteneces to get the best out of PBI and that it is only really meant for "proper" IT professionals and so forth......

[Anonymous]

The use of duplicate table and reference table, as well as the ability to create measures seems to be replicating a lot of the standalone objects that MS access can create etc, which you can then reuse to create calcs....

https://radacad.com/reference-vs-duplicate-in-power-bi-power-query-back-to-basics

https://blogs.technet.microsoft.com/cansql/2016/12/19/relationships-in-power-bi-fixing-one-of-the-co...