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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Filip_analyst
Regular Visitor

PowerQuery - Table.Profile() not able to calculate

Hello,

I have the following problem with Table.Profile() function. 
Standard output is as follows:

Filip_analyst_0-1661414705072.png

I need to perform Table.Profile() on table that has 130 columns and around 2 milion of rows (I need to know only Count and Null Count basically for each column. The rest of statistics is not required). With that, Table.Profile() is not able to handle (timout error). Unfortunately the action needs to be done on the whole table (business requirement).

Is there any way to calculate this in PowerQuery instead of on a database directly with T-SQL?
Appreciate any tips!



2 REPLIES 2
Filip_analyst
Regular Visitor

Thank you for quick answer.

I did a manual work, but the problem is not solved. In order to perform transformations PowerBI needs to switch from Direct Query to Import Node so all calculations are performed locally. I did several tests but it takes too long, so can not accept it. Finally, we wrote a T-SQL procedure to calculate the KPI's

HotChilli
Super User
Super User

I'm reasonably sure you can write your own version.

Unpivot the columns and Add a Custom Column (if value is "" then 1 else 0)

perform a 'Group By' with 2 aggregations (Count rows and Sum of the custom column)

Let me know how you get on

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors