Forum Discussion
Trimming text or date in time: Powershell API dataset
Using PowerBI for the first time...
I am using a Powershell script to retrieve date & time, text, and numerical values. For reporting I wanted to remove the time from the date and time value. Which I thought I had completed in the powershell script, but a default time of 0:00 or 12:00am gets added back to the values once in Powerbi. Additionally to clean up the presentation I want to remove 'https://www' from the URLs.
powershell date value = Get-Date -DisplayHint Date -Format MM/dd/yyyy
The above is working as expected when we run the script in powershell, but as I said a default value is added once it loads on desktop and report views for PowerBI.
| test_url | pagespeed_score | page_bytes | report_date |
| https://www.testurls.listedhere.com | 80 | 932229 | 3/1/2020 0:00 |
| https://www.testurls.listedhere.com | 79 | 1230234 | 3/1/2020 0:00 |
My question is can you point me to past conversations that may help me fix the formating. It looks like I am unable to use Modeling features due to pulling data from an API vs database. I want to remove characters from the start of URLS, and split date and time using Measures, so far with no luck.
I appriciate your feedback.
Hi, adam12asu
Based on your description, I created data to reproduce your scenario.
Table:
You may create two measures as follows.
testurlMeasure = var x = MAX('Table'[test_url]) return IF( LEFT(x,11) = "https://www", RIGHT(x,LEN(x)-LEN(LEFT(x,11))) ) reportdateMeasure = var _currentdate = MAX('Table'[report_date]) return MONTH(_currentdate)&"/"&DAY(_currentdate)&"/"&YEAR(_currentdate)Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
In Datatype choose Date and in Format choose the required format
amitchandak Thanks, but I am unable to select any of the Modeling values.
Hi, adam12asu
Based on your description, I created data to reproduce your scenario.
Table:
You may create two measures as follows.
testurlMeasure = var x = MAX('Table'[test_url]) return IF( LEFT(x,11) = "https://www", RIGHT(x,LEN(x)-LEN(LEFT(x,11))) ) reportdateMeasure = var _currentdate = MAX('Table'[report_date]) return MONTH(_currentdate)&"/"&DAY(_currentdate)&"/"&YEAR(_currentdate)Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-alq-msft Thank you that has saved me and imense amount of time and has formatted both data fields.
