Forum Discussion

Icon for Helper III rank

Helper III

6 years ago

NOOB Q! How to transform data across relationships

Hi gang, My first post, and definitely not my last! I'll be working on a project for the next month or two and will have no shortage of head scratchers, I'm sure! I've built some interesting stuf...

Icon for Community Champion rank

Community Champion

6 years ago

Mitch, you need to merge the tables in Power Query to do this. So merge Company to Company_Company, expand the Company_recID, do your filter, then delete the Company_RecID, then allow the Company table to be imported. Same with any other table you want to filter by before it loads.

If you do it right, 100% of this will be folded. The SQL Server will do all of the work, but then only return a minimal set of records you want.

Shpongle
Helper III
6 years ago
OK - The rust is coming off the gears now. While I haven't sorted it out 100%, it is clear to me the path I need to go - this makes sense. As such, I have marked your response as the solution. Many thanks edhans for taking the time to help me through this!
- edhans
  Community Champion
  6 years ago
  Great Shpongle . Glad I could help.
  
  It is very common for me to merge tables in Power QUery just for filtering, then remove those filtered columns before bring it into the DAX model. You have any further questions, post back and we'll see how we can help!
  - Shpongle
    Helper III
    6 years ago
    Yeah, I was able to figure it out! It's really easy to do once you know how to do it - I did end up going through those beginner tutorials and it was quickly evident. Being able to expand a table column (where a field contains table data) and choose the specific field I wanted made quick work - I would select the field I wanted and then filter on that column: Boom!
    
    I'm pretty sure that I will be merging tables pretty regularly. I really enjoy working with PowerBI - it's such a remarkable tool.
    
    Thanks again

Related content