Trimming text or date in time: Powershell API dataset

[adam12asu]

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.

[v-alq-msft · Accepted solution]

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.

[adam12asu]

@v-alq-msft  Thank you that has saved me and imense amount of time and has formatted both data fields.

[amitchandak]

In Datatype choose Date and in Format choose the required format

[adam12asu]

@amitchandak  Thanks, but I am unable to select any of the Modeling values.