PowerQuery: python script converts unrelated text columns to int

[travelsandbooks]

I have a table with a 19-digit numerical ID column, formatted as text, and another text column.

When I run a Python script in PowerQuery which manipulates the data in the text column, I've noticed that the ID column gets auto-converted to numbers - even when I don't reference that column in my script.

Initially my ID column looks like this:

Then I run the script and it looks like this:

I noticed that after running the script, PowerQuery automatically inserts a step that converts the ID column to Int64 - but even when I delete that step and prevent that happening, the Python script itself still converts the column to this:

How can I prevent the Python script from doing anything to my ID column, please?

[travelsandbooks · Accepted solution]

The solution to this was that the ID column, despite being formatted as text in the data model, was not formatted as text properly.

When I tried to make it the key column of the table, PowerBI told me that it had nulls, and therefore it couldn't be used.

I removed the null, and hey presto - the Python script worked as it should.

[lbendlin]

Don't include that column in your dataframe?

Maybe you can show a sanitized version of your Power Query code.

[travelsandbooks]

When you write a Python script, the entire dataset is passed in as "dataset". I only actually do work on the text column - I don't mention the ID column at all. That's why this chewing up of that column is so strange.

I could create a new table with only the text column, and do the work on that - but then joining back to the OG table using the long text string as a key doesn't feel like best practice.

Here is my code:

import requests 
import pandas as pd 

def fix(x): 
     try: 
          return (requests.head(x,allow_redirects=True,timeout=1)).url
     except: 
          return "Not available" 

dataset['urls2'] = dataset['urls'].apply(lambda x: fix(x))