webquery_to_powerbi

# Open PowerBi Desktop application
# Go to get data and select python.


#Import basic libraries 
import pandas as pd
import numpy as np
import lxml

# Create variable of Netsuite's webquery link
url = 'https://xxxxx.app.netsuite.com/app/reporting/webquery.nl?compid=xxxxxx&entity=-x&email=xxxxxxxxxxxxxx&role=x&cr=xxx&hash=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'

#Create dataframe using pandas of the webquery url
data = pd.read_html(url,flavor='lxml')

#Get first item of the list and convert into dataframe(Tabular form)
df = pd.DataFrame(data[0])

#Use first row as headers
header = df.iloc[0]
df = df[1:]
df.columns = header


#Remove special character from values
df['Amount'] = df['Amount'].str.replace('=',"").astype(float)

#Your dataframe is ready to load in powerbi



Option 2. 

import requests
import pandas as pd
import json
import lxml
from lxml import etree
import urllib3, urllib
from urllib.request import urlopen


url = 'https://xxxxx.app.netsuite.com/app/reporting/webquery.nl?compid=xxxxxx&entity=-x&email=xxxxxxxxxxxxxx&role=x&cr=xxx&hash=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
x = requests.get(url)
html = etree.HTML(x.content)
tr_nodes = html.xpath('//table/tr')

td_content = [[td.text for td in tr.xpath('td')] for tr in tr_nodes[0:]]

data = pd.DataFrame(td_content)

header = data.iloc[0]
data = data[1:]
data.columns = header