Reduce data

[Anonymous]

Hi,

I have the folowing situation

Table 1= Orders (400.000 rows)

Table 2 = Orderlines (1.500.00 rows)

Step 1. Get the data into PowerBI

Step 2. Filter table 1 (orders) for only year 2017

Step 3. Merge this 2 tables on the field orderID

Result:

With the merge table i have a dataset with orders and orderlines that i need filtered on year 2017.

Table 1 (orders) is reduced with the date field, so this not too big

But table 2 (orderlines) loads all the original data so the PowerBI file is verry large.

How can i reduce my PowerBI file or table orderlines?

With kind regard

Norbertus

[Anonymous · Accepted solution]

For now i made for all the tables i need in the Dashboard a SQL-View. So i can easily handle the input and the performance of the database / server is.

Unfortunately wasn't it posible to do it in PowerBI.

With the join in the "advanced options" the refresh time was verry long.

[Anonymous]

Interesting, I would think the performance should be similar. There are definitely benefits to having a view, but essentially you are still executing the same query when you select data  from a view.  But if this approach gives you shorter refresh times, then you are good to go.  Be sure to mark this thread as resolved.

[EvertonPessoa]

hi @Anonymous,

What is the origin of the base?

está usando uma consulta SQL?.

[Anonymous]

Hi,

yes, the data is stored in a SQL database.

I had thougt to make first for each table a view with the nessesarry data. Then is it also not required to make a mergetable in PowerBI because I can join the tables in the SQL View.

Where is the split to do the transaction in SQL or in PowerQuery?

[EvertonPessoa]

When you get the data you can put a SQL command instead of selecting the table in the structure. In this command you can add the filter to 2017 in the WHERE clause if this is the case:

[Anonymous]

Hi,

Is it also possible to place in this "advanced option" a SQL statement with a join to my orders table so i can filter on the field date?

My table "orderlines" has to be reduced with the filter year in the table "orders"

[EvertonPessoa]

You can do this through SQL if your goal is to reduce the amount of data to make the file lighter. If you do this inside Power BI, it will work but it will not reduce the file size. Your SQL will look something like this:

SELECT * FROM table2
WHERE table2.Year = (select DISTINCT Year FROM table1)

[Anonymous]

In Table 2 i don't have a date field.

I have to reduce my PowerBI size and also dateset.

Whats the way to go?

[EvertonPessoa]

@Anonymous If the solution Indicated by @Anonymous is not enough, put at least the table headers so we can help you better.

[Anonymous]

All you need is a join statement that will pre filter both tables, combine them and load them into Power BI.  Example:

Select * from

orders

join orderlines on orders.orderid = orderlines.orderid

where orders.orderdate between '01/01/2017' and '12/31/2017'

You can replace * with specific columns that you care about. Hope this helps.