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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ericOnline
Post Patron
Post Patron

Error when running Table.Profile()

Hello,

I'm trying to run Table.Profile() on a given table.

Receiving the error: "DataSource.Error: Microsoft Db2 Client: An expression with a datetime value or a labeled duration is not valid. SQLSTATE=42816 SQLCODE=-182"

Steps to reproduce:

- From PowerQuery Editor in PowerBI, click "New Source" then "Blank Query"

- In the formula bar, type: `= Table.Profile(tableName)`

Results:

image.png

Its unclear to me why there would be a Table.Profile() error due to a datetime field .

Any advice?

Thank you

6 REPLIES 6
123abc
Resident Rockstar
Resident Rockstar

The error message you're encountering, "An expression with a datetime value or a labeled duration is not valid. SQLSTATE=42816 SQLCODE=-182," suggests that there is an issue with a datetime value or labeled duration in the table you're trying to profile. The Table.Profile function in Power Query attempts to analyze the data in the specified table, including datetime values, and it seems there is a problem with one of these values.

Here are some steps you can take to troubleshoot and resolve the issue:

  1. Check the Data Types: Review the data types of all columns in your table, especially those that contain datetime values. Ensure that the data types are correctly specified, and there are no unexpected mismatches or inconsistencies.

  2. Check DateTime Values: Inspect the datetime values in your table to identify any outliers or values that might be causing the error. Look for any unusual or invalid datetime formats. You may need to clean or correct these values.

  3. Check for Null Values: If there are null values in columns with datetime data types, this can sometimes cause issues. Ensure that any null values are handled appropriately or converted to valid datetime values if necessary.

  4. Check for Invalid Durations: The error message also mentions "labeled duration." If you have any columns with duration values, ensure that they are correctly formatted and don't contain invalid values.

  5. Test with a Subset of Data: If your table is large, you might want to create a smaller subset of the data and try running Table.Profile on that subset. This can help you narrow down the specific rows or columns causing the issue.

  6. Inspect Query Steps: In Power Query, you can inspect the applied steps in the Query Editor to see if there are any custom transformations or calculations that might be affecting the datetime values. Check if any custom code or transformations are causing the error.

  7. Data Profiling Alternative: Consider using alternative data profiling techniques or tools if the issue persists. There are various data profiling tools available that can provide insights into your data without encountering the specific error you mentioned.

  8. Update Power BI: Ensure that you are using the latest version of Power BI. Sometimes, issues like this are resolved in newer software releases, so updating might help.

By following these steps, you should be able to identify and resolve the issue causing the error when running Table.Profile on your table. It's important to inspect your data and data types carefully to ensure they align with your expectations and the requirements of the profiling operation.

VossF
New Member

Try using Table.Buffer() before using Table.Profile() or nesting it Table.Profile( Table.Buffer( tableName ) )

v-alq-msft
Community Support
Community Support

Hi, @ericOnline 

 

There is something wrong with 'tablename' in 'Table.Profile(tablename)'. Could you please show us the query for 'tablename'? Do mask sensitive data before uploading.

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

edhans
Super User
Super User

It looks like something your DB2 provider is telling Power Query that it is a DateTime isn't a date time field.



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

Ok.

Table.Profile() looks all the way back to the data source then, eh? Not the processed/transformed table in Power BI?

Any ideas on how to rectify?

Table.Profile, to my understanding,  does it on the table that you feed it. It doesn't go back to the source. But something coming from your source is feeding it bad info. I'm only looking at your error message that references DB2, which I am assuming is an IBM database, DB/2, and Table.Profile() is close enough to see that.

 

Without more info, it is hard to say. I do know that mainframe DBs don't always do date/time the same way and it can cause Power BI problems until you massage the data so it fits the expected Date/Time format..



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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors