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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. 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
Community Champion
Community Champion

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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