cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GeekAlfPro
Advocate I
Advocate I

Excel power query strange behaviour

Hi there,

 

i created a power query script to get the max of a list.

it worked fine

 

but a few days later, i copy paste new values, and here is the problem.

despistes PQ did the job, i can't have the result in the excel file .

in the insight, the result is good but my table is empty and the error message says that there is no data source ....

thanks for your help

 

2021-02-11 10_20_55-NumMax PV 10022021.xlsx - Excel.png

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

Click on that link that says "there are 30 errors"

 

You should never ever have errors in your final query @GeekAlfPro 

You can see what the errors are by going into Power Query for that table and selecting Keep Rows on the home ribbon, then Keep Errors.

edhans_0-1613056145781.png

Then you have two choices:

  1. Fix the errors and remove the temporary Keep Errors filter
  2. If you don't care about the records at all, then remove the Keep Errors filter and do Remove Rows, Remove Errors from the home ribbon.

 



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

4 REPLIES 4
edhans
Super User III
Super User III

Frustrating yes, but not strange. I've seen that before. You then have to start digging deeper into the table to see why the error is showing up. It requires doing a lot of filtering and detective work. It may be as simple as you have more columns than you need and one of those unnecessary columns has the error. So remove any columns you don't need (if any) and see if it persists. If it does. you'll just have to dig in unfortunately. No magic bullet.



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

Yes, i will investigate.

i think it's a side effect.

Actually i use only one column but the file contains hundreds.

i will try again with a copy paste on another sheet, to be sure.

 

thanks again and have a good day

edhans
Super User III
Super User III

Click on that link that says "there are 30 errors"

 

You should never ever have errors in your final query @GeekAlfPro 

You can see what the errors are by going into Power Query for that table and selecting Keep Rows on the home ribbon, then Keep Errors.

edhans_0-1613056145781.png

Then you have two choices:

  1. Fix the errors and remove the temporary Keep Errors filter
  2. If you don't care about the records at all, then remove the Keep Errors filter and do Remove Rows, Remove Errors from the home ribbon.

 



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

hi @edhans,

thanks for taking time to have a look at my issue.

 

i've done what you suggested, but the error table is empty...

 

2021-02-11 18_48_26-Erreurs dans Tableau2 - Éditeur Power Query.png

 

Strange, isn't it ?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors