cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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 stuff in PowerBI in the past year and was a SQL admin and Access developer about ten plus years ago, with dabbling in stuff from time to time since then. I'm working with a SQL dataset in PowerBI for the first time and have run into a wall:

 

I have PowerBI connected to a SQL source. I presently am importing six of the tables. Following is a look at the relationships:

 

PBI_Query.png

 

What I want to do is limit the list of records imported from the [Company] table, as there are over 20k companies, but less than 250 of them are active. I want to do this to reduce the amount of time it takes to pull the data as well as to reduce load on the source SQL server.

 

I only want companies whose [Company_Type_RecID] (in green) is equal to a specific Description, which is listed in the [Company_Type] table. In other words, I only want Companies whose [Company_Type.Description]="Client".

 

I only want companies whose [Company_Status_RecID] (in blue) is equal to a specific Description. I want companies whose [Company_Status.Description]="Active"

 

I have tried using the Power Query Editor to set filters on the fields in the specific tables but I'm only succeeding in limiting the data in those particular tables, not the [Company] table.

 

I just need a nudge to get in the right direction - anybody able to offer insight on how to achieve this?

 

Thanks in advance,

 

Mitch

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: NOOB Q! How to transform data across relationships

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Re: NOOB Q! How to transform data across relationships

@Shpongle , You should able to filter in Data transformation mode .

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data

 

Another option is while adding the data from the connection you can get a query using advance option

AdvanceProperty.png



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
Helper III

Re: NOOB Q! How to transform data across relationships

Thanks, @amitchandak! I guess the problem is that while I know how to transform data across a single table, I'm not connecting the dots on how to do it when the data I want to use to restrict during the import is spread across tables. Everything I've tried has only restricted the flow of data on the one particular table but had no impact on the records coming in on the Company table...

 

LimitQuery.png

 

I can't use Company_Type_RecID to directly limit the records impoted from the [Company] table. This might be so basic that I'm over complicating it!

 

Any other suggestions?

 

Thanks again

Highlighted
Super User II
Super User II

Re: NOOB Q! How to transform data across relationships

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
Helper III
Helper III

Re: NOOB Q! How to transform data across relationships

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!

Highlighted
Super User II
Super User II

Re: NOOB Q! How to transform data across relationships

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!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Helper III
Helper III

Re: NOOB Q! How to transform data across relationships

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

Highlighted
New Member

Re: NOOB Q! How to transform data between relationships

This is the first step you take in BI, it's called ETL. We have to filter the trash

In your case it is done with nested queries in SQL. Imagine that I have a catalog of clients, historic, but prepare a study on the last year my main clients. The main customers based on what? If your answer is billing, then you should filter my customer catalog, based on multi-year invoices.

This query will give a single-column subtable with all customers (only their id) to the one that was billed last year and will be the filter for the Customers table

(SELECT DISTINCT IdCliente FROM TablaFactura WHERE (FechaFactura >= 01/01/2019 AND FechaFactura <= 31/12/2019))

Query to load customers.

SELECT (the fields you're interested in in the comma-separated customers table)

FROM TablaClientes

WHERE (SELECT DISTINCT IdCliente FROM TablaFactura WHERE (FechaFactura >= 01/01/2019 AND FechaFactura <= 31/12/2019))

I hope it helps.

Highlighted
New Member

Re: NOOB Q! How to transform data between relationships

Sorry there's an error in the query

SELECT (the fields you're interested in in the comma-separated customers table)

FROM TablaClientes

WHERE IdCliente IN (SELECT DISTINCT IdCliente FROM TablaFactura WHERE (FechaFactura >= 01/01/2019 AND FechaFactura <= 31/12/2019))

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors