Python Notebook: writing in existing table error due to datatype Null

[Testaaaaaa]

Hi All,

I have a existing table which was created (a few days ago) after pulling the last 30 days of activityEvents from PowerBI REST API.

I am facing issues when incrementally loading new data du to the following error:

Python Notebook: writing in existing table error due to datatype Null

I have the following code which I though would take care of this issue:

df = pd.DataFrame(all_items)
# Remove all columns with Null type
df = df.dropna(axis=1, how='all')
if not df.empty:
    df['Insert_Datetime'] = today[0:-6]
    table_path = f"{lakehouse_details_abfsPath}/Tables/{lakehouse_table}" 
    write_deltalake(table_path, df, mode='append', schema_mode='merge', engine='rust', storage_options=storage_options)
    print(f"        Number of records added in {lakehouse_table}: {len(df)}")

When query the data, I can't find columns with only NaN value.

I've tried to change NaN to None or other values to see if Nan was the issue but it doesn't seem so, especially after dropping columns being dropped if Nan value only.

Any tips on how I should go about?

[v-prasare]

@Testaaaaaa, As we haven’t heard back from you, we wanted to kindly follow up to check if your still facing same issue or let me know if you need additional help here.

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-prasare]

@Testaaaaaa, As we haven’t heard back from you, we wanted to kindly follow up to check if your still facing same issue or let me know if you need additional help here.

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-prasare]

@Testaaaaaa, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-prasare]

Hi @Testaaaaaa,

Thanks for reaching MS Fabric community support.

please refer to below code to ensure that Nan values are handled properly:

df = pd.DataFrame(all_items)

# Replace NaN with None for all columns
df = df.fillna(None)

# Replace NaN in any specific column with a placeholder value (for example, 0 for integers or a default date for datetime columns)
df['some_column'] = df['some_column'].fillna(0).astype(int)
df['Insert_Datetime'] = pd.to_datetime(today[0:-6], errors='coerce').fillna(pd.Timestamp('2025-01-01'))

# Drop columns that are completely NaN
df = df.dropna(axis=1, how='all')

# Check the data before writing
print(df.info())  # Check for any remaining NaN or None values

if not df.empty:
    table_path = f"{lakehouse_details_abfsPath}/Tables/{lakehouse_table}"
    write_deltalake(table_path, df, mode='append', schema_mode='merge', engine='rust', storage_options=storage_options)
    print(f"Number of records added in {lakehouse_table}: {len(df)}")

Thanks,

Prashanth Are

MS fabric community support

[Testaaaaaa · Accepted solution]

Hello,

Thank you for offering your help however this hasn't fixed the issue.

I ended up concatenating the data from the existing table with the new data as a workaround.

[v-prasare]

@Testaaaaaa, Thanks for confirmation.

As you resolved this with workaround, Can you please mark your post as answer so that other users who are facing similar challenges will able to resolve.

Thanks,

Prashanth Are

MS Fabric community support