Forum Discussion

pedromerry's avatar
pedromerry
New Member
4 years ago
Solved

Group by in cross table with binary variables without unpivoting

Hello, I have the following problem. I will show my example with much simpler data than I'm using to make it easier to explain:
I'm starting with a table such as this:

 

In my real scenario it's a table of profiling of clients wich has around 17 million rows, which is unfeasable for PowerBI Desktop. So I've converted the table to this other cross-table layout shown in Table B, which uses binary flags, to confirm if that exact combination is present. Changing to this table format has a huge compression effect in my case, as although I add 25 extra columns, the amount of rows goes down from 17 million to 2.5 million rows as there is a huge amount of duplicity in the numbers.


I would like to do a "groupby" with a Visual in powerbi and show the following resulting table:

 

So my problem is: 

a) if I try ingesting directly the data in Table A to PowerBI, it takes literally for hours and it turns unmanageable

b) if I ingest as in Table B and then do an unpivot columns in M Language in Power Query it also takes for hours...
c) If I Ingest as in Table B and do the unpivot columns in DAX Language (by doing a 'union' of unpivoted columns), the DAX query dies becuase of too much RAM demand (I have 16GB in my laptop).

So my question is, is there directly a way of showing the resulting data grouped as in Table C, while starting from Table B, directly, without unpivoting in PowerBI?. I'm trying to use the "Matrix" visual with no success...

Many thanks!

Pedro

3 Replies

  •  

     

    In my real scenario it's a table of profiling of clients wich has around 17 million rows, which is unfeasable for PowerBI Desktop

     

     

    That's a small amount of data and should not give issues in Power BI Desktop.  Are you trying to do data source merges?  Have you considered letting the data model do the grouping work for you?

     

    Please provide sanitized sample data that fully covers your issue.
    https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Please show the expected outcome based on the sample data you provided.

    https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Thank you every one for the replies. Finally I have managed to solve it by removing some information from the tables and unpivoting the data inside powerbi using powerquery and M language. I think the bottleneck here had to do with the speed of the database/db connector providing the data to ingest to PowerBI, as unpivoting in Spark and then ingesting to PowerBI took many hours v.s ingesting the pivoted table and unpivoting inside PowerBI with PowerQuery, which now takes aprox. 1.5 hours, which is feasable.

    Many thanks