Calculate date difference between two consecutive rows grouped by column using python script

Power Query
1

Hi all,

I am working on clinical data where I need to calculate sessions for patients by calculating date difference. I can do the same in python but the same script when used in power bi’s power query it gives error as below

DataSource.Error: ADO.NET: Python script error.
TypeError: unsupported operand type(s) for -: ‘str’ and ‘str’

Details:
DataSourceKind=Python
DataSourcePath=Python
Message=Python script error.
TypeError: unsupported operand type(s) for -: ‘str’ and ‘str’

ErrorCode=-2147467259

Script used by me is :-

‘dataset’ holds the input data for this script

import pandas as pd
import numpy as np
import datetime
dataset[‘Days_btw’] = dataset.groupby(‘NetSuiteId’)[‘session_date’].diff() / np.timedelta64(1, ‘D’)

This is link for data and pbix file

Expected output is as below which I got in python

CD
CD413×628 5.85 KB

Or is there any other way I can do this differently because I am importing this data from our production database.

Thank You.

2

@PranjaliK Store the date column as text and then use this:

let
    Source = Excel.Workbook (
        File.Contents ( "C:\Users\Pranjali.Khadse\OneDrive - Neuronetics, Inc\Desktop\Book1.xlsx" ), 
        null, 
        true
    ), 
    Sheet1_Sheet = Source{[ Item = "Sheet1", Kind = "Sheet" ]}[Data], 
    PromotedHeaders = Table.PromoteHeaders ( Sheet1_Sheet, [ PromoteAllScalars = true ] ), 
    ChangedType = Table.TransformColumnTypes (
        PromotedHeaders, 
        { { "NetSuiteId", type text }, { "session_date", type text } }
    ), 
    RunPythonScript = Python.Execute (
        "
# 'dataset' holds the input data for this script
import pandas as pd
import numpy as np
import datetime

dataset['session_date'] = dataset['session_date'].astype('datetime64[ns]')
dataset['Days_btw'] = dataset.groupby('NetSuiteId')['session_date'].diff() / np.timedelta64(1, 'D')", 
        [ dataset = ChangedType ]
    )
in
    RunPythonScript
1 Like
3

I will try this out. Thank you!

4

It worked!
Thank You for saving my day

1 Like