Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
wittr9876
Regular Visitor

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

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

 

 

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-yuezhe-msft
Employee
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors