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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Quick data inspection

In Pandas (Python library), you can call an inspect() function which returns details such as the total number of rows and the number of nulls and errors in each column. 

 

Is there any similar feature in Power Query? 

 

In particular, I like knowing exactly how many rows there are in each query, as I can then use this as a quick way to check if a merge has worked as expected. 

 

The attached photo shows an example of '999+' rows, which is not that useful! I appreciate that it may not be practicable to actually load millions of rows at a time, but surely just getting the number of rows is an easier task in computational terms? 

 

Thanks.row question.PNG

1 ACCEPTED SOLUTION

Something else you can try @Anonymous is using the built in visual components of Power Query below. Check out the View Tab in Power Query:

  • Column Quality (blue arrow) will show how many errors, empties, or valid cells there are in the current field.
  • Column Distribution (red arrow) will give an idea of how many are unique and the approx distribution of values.
  • Column Profile (green arrows) will take up half of your screen but provide a wealth of information on the selected column.
  • In the red box, you can tell the Column profile to use the full data set vs just 1,000 rows which is the default, but be forewarned, if you have a few million rows, it will take a minute to generate the profile. If you have tens of millions or hundreds of millions of records, I would not advise analysing the full data set that way unless you don't want to use your computer for a while. 😁

edhans_0-1600965682250.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
Jimmy801
Community Champion
Community Champion

Hello @Anonymous 

 

Try Table.Profile ( create a new query and input you queryname into this function). When you just need the rows use Table.RowCount.

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Anonymous
Not applicable

Thanks, will look into that solution asap and report back. Appreciated. 

Something else you can try @Anonymous is using the built in visual components of Power Query below. Check out the View Tab in Power Query:

  • Column Quality (blue arrow) will show how many errors, empties, or valid cells there are in the current field.
  • Column Distribution (red arrow) will give an idea of how many are unique and the approx distribution of values.
  • Column Profile (green arrows) will take up half of your screen but provide a wealth of information on the selected column.
  • In the red box, you can tell the Column profile to use the full data set vs just 1,000 rows which is the default, but be forewarned, if you have a few million rows, it will take a minute to generate the profile. If you have tens of millions or hundreds of millions of records, I would not advise analysing the full data set that way unless you don't want to use your computer for a while. 😁

edhans_0-1600965682250.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

@edhans Thanks, that's exactly what I was after. 

@Jimmy801  unfortunately I couldn't get your solution to work; I think I may have the syntax wrong? 

 

For the table TestTable, I created a new blank query, but none of the following worked: 

 

TestTable.Profile

Table.Profile(TestTable)

TestTable.RowCount

Table.RowCount(TestTable)

 

I am guessing I misinterpreted the intended formula? Thanks. 

@Anonymous do this to get Table.Profile to work.

  1. Do your query as normal.
  2. Right-click on that last step (or the step you want analyzed) and select Insert Step After
  3. In the Formula bar, wrap that in Table.Profile, so example:

 

= Table.Profile(#"Added Index")

 

Then delete that step when done with your analysis.

You can also create a new query that will always profile given query:

  1. New blank query
  2. put =Table.Profile(Queryname) in the formula bar. If the query has a space in it, it would be Table.Profile(#"Query Name") - and these are case sensitive.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors