Merging Tables In Power Query or DAX

[ChrisM32]

Hi All,

I'm pretty new to power bi, so my understanding way be way off.

The dataset I'm working with for is rather intensely fragmented and comes from Access databases. For example, I have a table called purchase order headers which will have the main info such as PO #, Supplier, Dates and another table called purchase order details which has all the item numbers, values, qty's etc, both tables can be linked via purchase ID #. Almost every table you'd expect to be setup as 1 is split in 2 or sometimes 3 tables. This results in me loading 10 tables to my report instead of 3-5. (I have no way of changing this at the source, it's how the IT guy built the system).

The course I have done have given me conflicting info. A Dax course I did said that everything apart from "data cleaning" should be done in DAX (including merging tables and creating new columns), another course I did said to do as much as possible in power query before the data is loaded and then use DAX for any measures.

The problem I am having is if I load all the tables (after stripping down what info I don't need) so I can use DAX, there are so many relationships that I get errors and it's also clunky and slow to load to report as well as confusing.

If I merge the 2 purchase order tables into 1 table, the 2 sales tables into 1 etc, will this then essentially refresh the merged table directly from the source independently as well as the 2 original tables, essentially refreshing 3 tables, or will it just refresh the original 2 and then pull the data for the merged table from the 2 refreshed original tables? I am more confortable in power query due to excel so this would be prefered over DAX.

Apologies if I have not explained this well. I basically want to check if merging source tables together as a new table will further slowdown the refresh process, as we have other systems that refresh from the source data.

[HotChilli · Accepted solution]

"When power bi refreshes from the source file" -   There is a function called 'query folding' in which queries get pushed to the source system. In your case I have no idea whether it will fold but you can investigate using this information: https://learn.microsoft.com/en-us/power-query/power-query-folding 

Power Query can also do things you might not expect like load the same table multiple times.

However, you should be able to uncheck 'Enable load' for the tables which are being used as intermediate tables for the merge.  This will prevent them being loaded to powerbi front end.

--

I think if I was you I would focus on getting the data model right since that is the basis for everything

[HotChilli]

"there are so many relationships that I get errors and it's also clunky and slow to load to report as well as confusing." -> Get your data model right.  Use a star schema bearing in mind what the reporting requirements are.  Control the relationships yourself rather than leaving it to powerbi to define them.

This paragraph that starts "The course I..." - as much as possible do the work in Power Query. Data modelling, cleaning, reshaping.  However, there are exceptions.  If it is easier and performance is not hampered and you have the skills in DAX, do that.

This paragraph that starts "If I merge..." is a bit confusing.  What is the problem you are trying to solve?  Merges can be slow but relatively easy to do and a powerful technique.  If it's an access source the data can't be THAT large, can it?

[ChrisM32]

"Use a star schema bearing in mind what the reporting requirements are.  Control the relationships yourself rather than leaving it to powerbi to define them."

- I have attempted to do the relationships myself but I still run into "indirect relationships which will cause ambiguity" errors, and depsite a lot of googling I still haven't figured out how to avoid this, yet.

"What is the problem you are trying to solve?  Merges can be slow but relatively easy to do and a powerful technique.  If it's an access source the data can't be THAT large, can it?"

- Basically I will have to make reports based on purchasing data or instance, but this data is split into 2 tables. My main question here is that if I take 2 of the source tbales "Purchase Order Header" & "Purchase Order Details" and merge them into one table as new (so I will have 3 tables in power query). When power bi refreshes from the source file, will it refresh all 3 tables independantly directly from the source or will it refresh the 2 original tables directly from the source, and then use the 2 refreshed table to then refresh the merged table within powerbi, so that the merged table is not directly pulling data from the source.

Every category within the business that I will report on will be setup like this. So will merging the multple tables into 1 easier to work with table am I essentially just make more tables that need to communicate with the source, slowing down the refresh time? As our other systems can not refresh/backup whilst powerbi is refreshing.

At the moment I have 13 tables pulled from the source (the biggest only having just over 600k rows), these could easily be merged to reduce this to at least 5/6 tables which actually need to load into the report, and with more connections to add with the same problem of fragmented data, I'm just trying to find the best way to gain a balance of speeding up refreshes and making it easier to work with.