Forum Discussion

Chris_Wilson's avatar
Chris_Wilson
New Member
6 years ago

Power Query Python dataset converting columns to strings

I have a simple test script as follows:   import pandas as pd dataframe_info = pd.DataFrame(dataset.dtypes) dataframe_copy = pd.DataFrame(dataset) dataframe_obj = pd.DataFrame(dataset.loc[:,"Obj...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Chris_Wilson 

When importing with Power BI, it would reserve the original type.

As searched, it is said pd.dataframe can't read binary file correctly.

We need to find some python functions to deal with the binary type data.

As a tempory workaround, you could convert the binary type firstly in Power BI, then use python scripts.

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Chris_Wilson's avatar
Chris_Wilson
New Member
6 years ago

Maggie:

 

I don't know how to attach images to a post without uploading to a URL, so I'll have to describe what I'm seeing.

 

  1. Start PBI (latest version), select "Get data" the SQL Server, enter servername (I'm using localhost\sqlexpress), database, and Import
  2. Select table then Load
  3. Click Transform data to open Power Query editor
  4. Table headings show correct field type (string, date-time, float, binary). I am aware at this point I could click "Combine files" in the binary column and PQ would be able to interpret selected binary formats like PDF.
  5. Select Transform group then Run Python script
  6. Enter this script: 
    # 'dataset' holds the input data for this script
    import pandas as pd
    dataset_types = pd.DataFrame(dataset.dtypes)
    dataset_copy = dataset.copy()
  7. The result shows dataset, dataset_copy, dataset_types. Click dataset: a new step called dataset is created as well as Changed Type (which I know is configurable)
  8. All the columns in dataset are strings and every cell of the binary column contains says "System.Byte[]"
  9. The Changed Type step does type conversions for the non-null columns, but of course doesn't fix the binary column.
  10. Interestingly, when you look at the dataet_types dataframe, the types look correct. It appears that the process of operating on the dataset dataframe screws up the types.

I don't expect PQ to decypher my binary contents (that's why I'm writing the Python code).

 

Thanks,

 

Chris