Power BI and Python Script keep changing the date type

Question

I am trying to run a python script in Power BI, in my database I have these set of abbreviation that I need to convert to their full form in a different column so for that I am writing a python script. It does the job perfectly but what else it does is that it changes the type of the columns which messes up with my date column and then after the script is run it changes the type the date becomes Microsot.ole.db.date

This is my script below:

# Get the table data
table = dataset.copy()

# Define the function to apply the condition
def update_job(job):
    # Extract the abbreviation from the job
    abbreviation = job.strip()[:4]
    
    # Check if the abbreviation exists in the dictionary, if yes, return the corresponding full form
    if abbreviation in abbreviations_to_full_forms:
        return abbreviations_to_full_forms[abbreviation]
    else:
        return job

# Apply the function to the 'Job' column and create a new column 'Full Job'
table['Full Job'] = table['Job'].apply(update_job)

# Function to convert to date with error handling
def convert_to_date(val):
    try:
        return pd.to_datetime(val).date()
    except (ValueError, AttributeError):
        return val

# Convert date columns to date
date_columns = ['Actual_Start_Date', 'Misc_Title_3']  # Add your date columns here
for column in date_columns:
    table[column] = table[column].apply(convert_to_date)

I tried manually changing the type, but the entire column gets filled up with errors; as you can see in the above code, I did apply some error handling as well, but it still does the same.

I want to prevent it from changing its type after running the python script.

stackoverflow.com/questions/41823277/… This could be used as an answer. Thank YOu — Sky_0820
– Sky_0820, Commented Feb 21, 2024 at 13:56

user9517769 · Accepted Answer · 2024-02-21 14:21:26Z

0

Try adjusting your code like below :

import pandas as pd

def convert_to_date(val):
    try:
        return pd.to_datetime(val).normalize()
    except (ValueError, AttributeError):
        return val

date_columns = ['Actual_Start_Date', 'Misc_Title_3']  
for column in date_columns:
    table[column] = table[column].apply(convert_to_date)

I just tried to keep the dates in a format that is more likely to be recognized by Power BI as dates, without converting them to date objects.(I added .normalize() to make sure that the time component is set to midnight, standardizing the datetime objects)

answered Feb 21, 2024 at 14:21

user9517769

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Power BI and Python Script keep changing the date type

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related