cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
borlip Regular Visitor
Regular Visitor

Executing Table.Group on SQL Server

We are using SQL Server as a source of Power BI.

 

My Query has Table.Group() function. Seems like power bi doesn’t know how to translate Table.Group() into tsql GROUP BY. As a result, it downloads enormous amount of records from the sql server into the memory and then executes grouping in the memory of Power BI Designer!!!

As a result, it takes forever and there is a huge traffic impact on our network.

1 ACCEPTED SOLUTION

Accepted Solutions
Power BI Team curth
Power BI Team

Re: Executing Table.Group on SQL Server

Yeah Smiley Sad.

 

There's an outstanding medium-term work item to give better visibility in PBI Desktop for what runs on the server vs locally, so you don't have to discover the hard way. In principle, there's no reason we couldn't calculate the start of the week on the server, but there's a really long tail of functions we don't currently fold and no obvious way to prioritize between them. If there are specific functions like Date.StartOfWeek that you think are critical to support, consider filing a request at UserVoice for those particular functions.

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Executing Table.Group on SQL Server

This is correct, Power Query uses M code and is not translating this into native functions. In addition, the tabular data modeling of Excel and Power BI Desktop are in memory technologies. I would recommend that, as a source, you include the SQL Group By clause in your initial source query or create a stored procedure in SQL that includes the Group By clause and simply EXEC that stored procedure.

 

What does your SQL source select statement currently look like?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
Power BI Team curth
Power BI Team

Re: Executing Table.Group on SQL Server

Whether or not a given invocation of Table.Group can be pushed to run on the back end depends on the kind of aggregate operations you're performing inside the grouping. For instance,

 

= Table.Group(Source, {"GroupId"}, {{"Count", each Table.RowCount(_), type number}})

 

might fold because Table.RowCount is supported on the back-end, but

 

= Table.Group(Source, {"GroupId"}, {{"Median", each List.Median([Price]), type number}})

 

might not, because List.Median isn't supported in SQL.

 

(This assumes, of course, that there wasn't an earlier operation which blocked folding of subsequent operations.)

borlip Regular Visitor
Regular Visitor

Re: Executing Table.Group on SQL Server

I've been doing Sum, which should be supported by tsql.

#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Customers.CustomerSubCode", "Carrier", "StartOfWeek"}, {{"FreightCharges", each List.Sum([FreightCharges]), type number}, {"TotalNett", each List.Sum([TotalNett]), type number}})

borlip Regular Visitor
Regular Visitor

Re: Executing Table.Group on SQL Server

You were right, Group is converted to tsql. However, I my case one of the group keys has gone through Transformation (StartOfWeek). StartOfWeek can't be translated to tsql and it causes the entire table with millions of rows to get downloaded to the client.... Smiley Sad

 

 

let
Source = Sql.Database("AFS-SQL01", "AFSXchange"),
dbo_BuyConsignments = Source{[Schema="dbo",Item="BuyConsignments"]}[Data],
#"Calculated Start of Week" = Table.TransformColumns(dbo_BuyConsignments,{{"ConsignmentDate", Date.StartOfWeek, type datetime}}),
#"Grouped Rows" = Table.Group(#"Calculated Start of Week", {"Customer_Id", "Carrier", "ConsignmentDate"}, {{"Count", each List.Sum([FreightCharges]), type number}})
in
#"Grouped Rows"

Power BI Team curth
Power BI Team

Re: Executing Table.Group on SQL Server

Yeah Smiley Sad.

 

There's an outstanding medium-term work item to give better visibility in PBI Desktop for what runs on the server vs locally, so you don't have to discover the hard way. In principle, there's no reason we couldn't calculate the start of the week on the server, but there's a really long tail of functions we don't currently fold and no obvious way to prioritize between them. If there are specific functions like Date.StartOfWeek that you think are critical to support, consider filing a request at UserVoice for those particular functions.

View solution in original post

borlip Regular Visitor
Regular Visitor

Re: Executing Table.Group on SQL Server

Visibility around what is done in memory and what is executed remotely, as well as the impact on the network is very important. The tool is meant to be used by not technical people, but the impact might be very bad.

I'll add to the user voice. I'm surprised that no one noticed it before....

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 326 members 3,252 guests
Please welcome our newest community members: