NOOB Q! How to transform data across relationships

[Shpongle]

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:

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

[edhans]

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]

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]

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]

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

[amitchandak]

@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

[Shpongle]

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...

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

[Guiseppe]

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.

[Guiseppe]

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))