Python script changes data in date column.

[DnsLeu]

Hello!

After running a python script in power query, one of my columns containing date data is completely changed to some text. The column is not even part of the python script. All the dates are replaced with "Microsoft.OleDb.Date". 

The source data is an Excel file located on SharePoint. Any idea as to what could cause it or how to revert the data back?

Best regards,
Denis

 

[Zoyashah]

A Python script modifies data within a date column, facilitating dynamic data manipulation and analysis. Ensure accuracy and consistency in your dataset by implementing error handling and validation mechanisms. Document your script's functionality and modifications in branded notebooks, providing a dedicated space to track changes and iterations. Stay organized and efficient in your data processing endeavors as you leverage Python's versatility to enhance your dataset's quality and relevance.

[lbendlin]

The column is not even part of the python script

Then how else does it participate in the output?  Your python script is supposed to create a dataframe that is handed back to Power Query as a table.

[DnsLeu]

Hey,

I have 17 columns. The script that I run only adds a new column at the end. The results in this column are based on the values in 3 of columns. 

When I run the script, I get all the original dataframe back, with all 17 columns + 1 (the 1 computed by python). In these original 17 columns, only 1 of them (presented above) gets its values changed. The other 16 columns maintain their values. 

Perhaps I did not do a good job clarifying in the first place. 

[lbendlin]

Maybe you can show a sanitized version of the Power Query script.

[DnsLeu]

Sure!

# 'dataset' holds the input data for this script 

import pandas as pd 

def calculate_Ordering(row): 

    curr_row = row['Chapter'] 

    row_grp = row['Name'] 

    row_main = row['Main'] 

    prj = row['Project'] 

    main_groups = dataset[(dataset['Main'] == True) &  

                     (dataset['Name'] != row_grp) &  

                     (dataset['Project'] == prj)] 

    rel_chpt = main_groups[main_groups['Chapter'] < curr_row]['Chapter'].max() 

    if row_main: 

        return curr_row 

    else: 

        if str(curr_row).startswith(str(rel_chpt)): 

            return rel_chpt 

        else: 

            return curr_row 

dataset['Order'] = dataset.apply(calculate_Ordering, axis=1) 

print(dataset)

The column affected is "Deadline", which only has date type data. It is not included in the script and is the only one affected. A possible work around would be to have it as text before running the Python script and then convert it back to date type, but this still does not explain why the data gets lost / modified in the first place.

[lbendlin]

There is no point in the last print(dataset)  command. 

your dataset will completely replace any prior output, including that date column.

I'd need to see more of the Power Query script before and after the Python part  to be able to assist more.

[DnsLeu]

Prior and after the script there are few Power Query steps like renaming and replace value. I removed print(dataset), but has the same result.

Right now, the only solution I have found is just to transform all the columns to text before running the script and then transform them back to their respective type after the script. 

Thanks for the help anyways!