What is best - use SQL Server function to shape data, or entirely use GetData?

[wittr9876]

Greetings.

I have a situation where I currently use a SQL Server function to assemble the data for a report. The SQL Server code constructs a number of custom columns. These custom columns use CASE or IF statements to evaluate/compare values in other columns in the table; based on those conditions, the custom columns are assigned various values. There are also various filter conditions.

I can recreate these aspects using the data modeling features in PowerBI. However, it is definitely more time-consuming than simply using a SQL Server query, requires many steps in the query editor, & I think is more difficult for another developer to simply step in & understand.

What I am wondering is, in a situation like this, (let's say I was starting from scratch, with no prebuilt SQL Server function), is it better to simply construct a SQL Server query/function to use as a data source for PowerBI, or is it recommended instead to always use PowerBI's query editor, pull in the raw tables, & perform any data shaping & filtering there?

Thanks,
Randy

[Anonymous · Accepted solution]

@wittr9876,

It depends on your requirement, if you are good at T-SQL, you can create SQL Server function and call the function in Power BI Desktop to shape data or filter data. And when you have large amount of data and columns in the raw tables, and you are clear about which columns and rows are useful in Power BI report, it is better to use SQL Server query/function to limit the columns and rows, which will boost performance on Power BI side. Many complex steps against large table  in Power BI Desktop query editor will put pressure on memory.

However, if you are not familiar with T-SQL, you can import all data into Power BI Desktop and transform data in Query Editor as it is easy and convenient to use.

Regards,
Lydia