Forum Discussion
Python script is not working with Regex contains "|" or "*"
Appreciate for your help in advance.
I built regex based text extract codes to extract the timestamp information from freetext.
# 'dataset' holds the input data for this script
regex = r'(((\d{1})|(\d{2}))/((\d{1})|(\d{2}))/((\d{2})|(\d{4}))\s\d{2}:\d{2}:\d{2}(\s[aAPp][mM])*)'
dataset['Timestamp'] = dataset['description'].str.extract(regex)
The regex is valid, but output with some error messages in below. But the code works with some simple regex without "|" or "*"
DataSource.Error: ADO.NET: Python script error.
Traceback (most recent call last):
File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\core\indexes\base.py", line 2897, in get_loc
return self._engine.get_loc(key)
File "pandas\_libs\index.pyx", line 107, in pandas._libs.index.IndexEngine.get_loc
File "pandas\_libs\index.pyx", line 131, in pandas._libs.index.IndexEngine.get_loc
File "pandas\_libs\hashtable_class_helper.pxi", line 1607, in pandas._libs.hashtable.PyObjectHashTable.get_item
File "pandas\_libs\hashtable_class_helper.pxi", line 1614, in pandas._libs.hashtable.PyObjectHashTable.get_item
KeyError: 'Timestamp'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\core\internals\managers.py", line 1069, in set
loc = self.items.get_loc(item)
File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\core\indexes\base.py", line 2899, in get_loc
return self._engine.get_loc(self._maybe_cast_indexer(key))
File "pandas\_libs\index.pyx", line 107, in pandas._libs.index.IndexEngine.get_loc
File "pandas\_libs\index.pyx", line 131, in pandas._libs.index.IndexEngine.get_loc
File "pandas\_libs\hashtable_class_helper.pxi", line 1607, in pandas._libs.hashtable.PyObjectHashTable.get_item
File "pandas\_libs\hashtable_class_helper.pxi", line 1614, in pandas._libs.hashtable.PyObjectHashTable.get_item
KeyError: 'Timestamp'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "PythonScriptWrapper.PY", line 14, in <module>
dataset['Timestamp'] = dataset['description'].str.extract(regex)
File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\core\frame.py", line 3472, in __setitem__
self._set_item(key, value)
File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\core\frame.py", line 3550, in _set_item
NDFrame._set_item(self, key, value)
File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\core\generic.py", line 3381, in _set_item
self._data.set(key, value)
File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\core\internals\managers.py", line 1072, in set
self.insert(len(self.items), item, value)
File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\core\internals\managers.py", line 1181, in insert
block = make_block(values=value, ndim=self.ndim, placement=slice(loc, loc + 1))
File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\core\internals\blocks.py", line 3267, in make_block
return klass(values, ndim=ndim, placement=placement)
File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\core\internals\blocks.py", line 2775, in __init__
super().__init__(values, ndim=ndim, placement=placement)
File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\core\internals\blocks.py", line 128, in __init__
"{mgr}".format(val=len(self.values), mgr=len(self.mgr_locs))
ValueError: Wrong number of items passed 11, placement implies 1
Details:
DataSourceKind=Python
DataSourcePath=Python
Message=Python script error.
Traceback (most recent call last):
File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\core\indexes\base.py", line 2897, in get_loc
return self._engine.get_loc(key)
File "pandas\_libs\index.pyx", line 107, in pandas._libs.index.IndexEngine.get_loc
File "pandas\_libs\index.pyx", line 131, in pandas._libs.index.IndexEngine.get_loc
File "pandas\_libs\hashtable_class_helper.pxi", line 1607, in pandas._libs.hashtable.PyObjectHashTable.get_item
File "pandas\_libs\hashtable_class_helper.pxi", line 1614, in pandas._libs.hashtable.PyObjectHashTable.get_item
KeyError: 'Timestamp'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\core\internals\managers.py", line 1069, in set
loc = self.items.get_loc(item)
File "C:\Users\admin\AppData\Local\Programs\Python\Python37\lib\site-packages\p...
ErrorCode=-2147467259
ExceptionType=Microsoft.PowerBI.Scripting.Python.Exceptions.PythonScriptRuntimeException
3 Replies
Hi Anonymous ,
I'm not good at Python script, are you sure your python script correct?
By my test with the script below in Query Editor, there is no any error. So I 'm afraid that Python script is working with Regex contains "|" or "*" .
From the blog, we could know how to use "|" and "*" in Python script.
# 'dataset' holds the input data for this script import re pattern = '^a|b..cd*n$' test_string = 'abycdn' result = re.match(pattern, test_string) if result: print("Search successful.") else: print("Search unsuccessful.")Please check your script again, if that is completely correct and you still have problem in Power BI , please let me know.
Best Regards,
Cherry
Hi v-piga-msft Cherry,
Thanks for looking into my issue. The Python script is correct, if I use is for a simple regex pattern. Like the below one.
# 'dataset' holds the input data for this script
regex = r'(\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2})'
dataset['Timestamp'] = dataset['description'].str.extract(regex)So, it is only not working when the regex become complex somehow. Does POWER BI offers any native filter option based on the regex, then the Python script is not really necessary.
Hi Anonymous v-piga-msft,
I am having the same issue using a Python script with regular expressions in Power BI. Did you find a solution to run the script? Please let me know. You can also reply to my post here. Thank you!
