Forum Discussion
Advanced ODBC Query - Pulling through a Summarized/Pre-Merged Column
lbendlin InMaster is a separate table to InTrans but it's stored in the server as an expandable column in InTrans.
I know this because if I import the entire table I can see the column and click a drop down to select the columns from InMaster that I want to expand for.
It looks exactly the same as you'd expect it to you if you did a merge in Power Query between two tables but I just can't pull it through using the SQL query.
The only thing I've been able to do to get it to kind of work is do an inner join in the SQL and state exactly which column from InMaster I want.
Yes, that's what you are supposed to do. Run a native SQL query with all required joins. Does that not work for you?
lbendlin I'm able to pull through the specific column I want using a join in the native SQL query I've written in the advanced editor.
I was hoping that I would be able to pull through the entire expandable InMaster column instead of having to ask for individual columns during the join.
Thinking about it now, it's probably possible to bring through that expandable column by editing the SQL I have - I don't know SQL too well so I'd have to do more research.
Saying that, it's probably best practice only to ask the server for the columns I need instead of trying to drag the whole table in, isn't it?oh, that's a very deep question. It is a conflict between trying to be lean, and the ability to "answer the next question". It requires you to have deep knowledge of your data and of the requirements of your business users, often before they know what these are.
There's a phrase that I like "premature optimization is the root of all evil". The general rule is - bring in as many columns as you can until you hit performance issues. Only remove columns where you are absolutely sure they contain no information (and be prepared to re-include them anyway).
Time dedicated to carefully designing your data model is time well spent. Time spent on Power Query merges is wasted time.
lbendlin Thanks for that - interesting thought on bringing as much in as possible instead of bringing everything through. I've traditionally thought that I should only bring in what I need to try and keep capacity requirements as low as they can be. I see what you're saying with the concept though.
I generally only use relationships throughout all my tables - this one being the exception as the join was on two different pieces of information and I wasn't sure how best to replicate this in Model View. The last time I had to do something similar I had to use a combination of several measures and calculated columns - it was also a much more complex scenario though.