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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Microsoft Employee
Microsoft 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
Microsoft Employee
Microsoft 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors