Poor Performance while joining SQL server table with csv file based on the condition

[amshukla]

Hi All,

I have Query1 which has imported data from SQL server table which has 2.3M records with 2 columns.

IpAddress_Table(Query1):

IpAddress

IpAddress_Decimal

Query2 is the CSV file which has 5 columns which as 25K records:

Query2:

Column1

Column2

Column3

Host_Min_Decimal

Host_Max_Decimal

I have added one new custom column in Query 2 using below formula, this column is giving the IpAddress value from Query1 which is in the range of Host_Min_Decimal and Host_Max Decimal.

Table.SelectRows(IpAddress_Table, (IpAddress_Table) => IpAddress_Table[IpAddress_Decimal]>=[Host_Min_Decimal] and IpAddress_Table[IpAddress_Decimal]<=[Host_Max_Decimal])[IpAddress]

The problem is while saving the data model it is taking indefinate time due to which i am unable to save it.

Please let me know how to resolve this issue.

Regards,

Amit

[amshukla]

@ImkeF Could you please have look on this, really need your help.

[ImkeF]

Yes, Power BI will by design not fold to the server if you filter by non-SQL-sources. Please use this workaround: http://www.thebiccountant.com/2017/07/20/sql-query-folding-bug-still-alive-sucking-powerbi-powerquer...

You need to adjust the step "Result" to the logic your filtering-command from above.