OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

PowerBI: json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)

  • Thread starter Thread starter Kartikey
  • Start date Start date
K

Kartikey

Guest
I am trying to extract data from API and create dataframe using that data in PowerBI to create visualizations dashboards using the following script :

Code:
import pandas as pd
import pprint
import json
import time
import requests

class EmployeesData():
    def __init__(self):
        self.token = "dummmmmmmmmmmmy"

    def get_employee_data(self, url_id):
        """
        Returns Employee Data
        """
        data_endpoint = "https:/dummy_endpoint_two/people/{}".format(url_id)
        
        payload = {
            "fields": ["work.department", "root.id", "home.legalGender", "payroll.employment.contract", "ethnicity", "root.displayName"],
            "humanReadable": "REPLACE"
        }

        headers = {
            "accept": "application/json",
            "content-type": "application/json",
            "authorization": "Basic {}".format(self.token)
        }

        response = requests.post(data_endpoint, json=payload, headers=headers)
        employee_data = response.json()

        return employee_data


    def fetch_employees_url_id(self):
        """
        Fetch employee ID from the api endpoint
        """

        required_data = []
        parent_endpoint = "https://dummmmy_endpoint_one/"

        payload = {
            "humanReadable": "REPLACE",
            "showInactive": True
        }
        headers = {
            "accept": "application/json",
            "content-type": "application/json",
            "authorization": "Basic {}".format(self.token)
        }

        response = requests.post(parent_endpoint, json=payload, headers=headers)
        data = response.json()

        count = 0

        for emp_data in data["employees"]:
            employee_data = self.get_employee_data(emp_data["id"])
            if employee_data:
                extracted_data = {
                    'Department': employee_data.get('work', {}).get('department'),
                    'ID': employee_data.get('id'),
                    'Gender': employee_data.get('home', {}).get('legalGender'),
                    'Contract': employee_data.get('payroll', {}).get('employment', {}).get('contract'),
                    'Ethnicity': employee_data.get('custom', {}).get('ethnicity', {}).get('ethnicity'),
                    'DisplayName': employee_data.get('displayName')
                }
                required_data.append(extracted_data)
            count += 1
            if count % 100 == 0:
                print("Reached 100 API calls, waiting for 1 minute...")
                time.sleep(60) 
        
        if required_data:
            df = pd.DataFrame(required_data)
            return df


object = EmployeesData()
df = object.fetch_employees_url_id()
df.to_excel('/Users/kartikeysinha/Desktop/local-git-repository/dump/Employee_Details.xlsx', index=False)
pprint.pprint(df)

While same script is running fine in my local system as it is successfully creating the excel file but the same is not running in powerBI python script source to create dataframe and I am getting following error :

Code:
ADO.NET : Python script editor.
<pi>json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)
During Handling of the above exception another exception occurred:

requests.exception.JSONDecodeError:Expecting value : line 1 column 1 (char 0)
</pi>

Any help would be really great thanks and best regards :)
<p>I am trying to extract data from API and create dataframe using that data in PowerBI to create visualizations dashboards using the following script :</p>
<pre><code>import pandas as pd
import pprint
import json
import time
import requests

class EmployeesData():
def __init__(self):
self.token = "dummmmmmmmmmmmy"

def get_employee_data(self, url_id):
"""
Returns Employee Data
"""
data_endpoint = "https:/dummy_endpoint_two/people/{}".format(url_id)

payload = {
"fields": ["work.department", "root.id", "home.legalGender", "payroll.employment.contract", "ethnicity", "root.displayName"],
"humanReadable": "REPLACE"
}

headers = {
"accept": "application/json",
"content-type": "application/json",
"authorization": "Basic {}".format(self.token)
}

response = requests.post(data_endpoint, json=payload, headers=headers)
employee_data = response.json()

return employee_data


def fetch_employees_url_id(self):
"""
Fetch employee ID from the api endpoint
"""

required_data = []
parent_endpoint = "https://dummmmy_endpoint_one/"

payload = {
"humanReadable": "REPLACE",
"showInactive": True
}
headers = {
"accept": "application/json",
"content-type": "application/json",
"authorization": "Basic {}".format(self.token)
}

response = requests.post(parent_endpoint, json=payload, headers=headers)
data = response.json()

count = 0

for emp_data in data["employees"]:
employee_data = self.get_employee_data(emp_data["id"])
if employee_data:
extracted_data = {
'Department': employee_data.get('work', {}).get('department'),
'ID': employee_data.get('id'),
'Gender': employee_data.get('home', {}).get('legalGender'),
'Contract': employee_data.get('payroll', {}).get('employment', {}).get('contract'),
'Ethnicity': employee_data.get('custom', {}).get('ethnicity', {}).get('ethnicity'),
'DisplayName': employee_data.get('displayName')
}
required_data.append(extracted_data)
count += 1
if count % 100 == 0:
print("Reached 100 API calls, waiting for 1 minute...")
time.sleep(60)

if required_data:
df = pd.DataFrame(required_data)
return df


object = EmployeesData()
df = object.fetch_employees_url_id()
df.to_excel('/Users/kartikeysinha/Desktop/local-git-repository/dump/Employee_Details.xlsx', index=False)
pprint.pprint(df)
</code></pre>
<p>While same script is running fine in my local system as it is successfully creating the excel file but the same is not running in powerBI python script source to create dataframe and I am getting following error :</p>
<pre><code>ADO.NET : Python script editor.
<pi>json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)
During Handling of the above exception another exception occurred:

requests.exception.JSONDecodeError:Expecting value : line 1 column 1 (char 0)
</pi>
</code></pre>
<p>Any help would be really great thanks and best regards :)</p>
 

Latest posts

Top