Getting seconds from SharePoint list date/time column

[Anonymous]

I'm using Power Query in Power BI Deskop RS to read a SharePoint list. In that list there are several date/time fields, for example "Started".

The Started field contains a date and a time. By default, my SharePoint site does not display seconds. I formatted the Started column with some JSON so that seconds are displayed, so I can now see them and know they are there. 

I also have an Excel spreadsheet where I have a power query to read the list. In my spreadsheet I can see/read/load the seconds value of Started.

So in the SharePoint list and in my Excel file I can see that the Started field lists seconds for all rows (and not just :00, actual real second numbers of all different values from 00 to 59) for the Started field.

In my Power BI report, I need you have access to these second values on these dates (not important why), but all the values listed for Started in Power BI Power Query or the Power BI Data window, display as mm/dd/yyyy hh:mm:00. I thought maybe the seconds were actually there and were just being displayed as :00 so I created a calculated column where I converted my Started date/time power query column to a text column, and that resulted in :00 seconds also.

I am using Implementation 2.0 of the SharePoint connector. It is my understanding that if I were to use old implementation 1.0, I would retrieve the date/time value with seconds included, but I don't want to use impletantion 1.0 of the connector.

So I can't get seconds in my Power BI report or am I missing something?

I'm guessing I can't get seconds unless I have to do some sort of back flips like create some sort of new calculated column in the SharePoint list of type text that has the date and time including seconds and then read that column into Power BI. I shouldn't have to, and don't want to, do that. I'm hoping that the seconds are actually already in my Power Query/BI Started field somehow and there is some trick available to expose/use them.

Comments, thoughts, suggestions, corrections?

Thank you, Jess

[lbendlin · Accepted solution]

It's a known "feature"  of the v2 connector.  If you don't like that, you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues . If you have a Pro license you can consider raising a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi

If you are adventurous you can also use the SharePoint REST API to fetch the list items. That includes the UTC timestamp down to the seconds

let
    Source = Json.Document(Web.Contents("https://<site url>/_api/web/lists/GetByTitle('Microsoft tickets')/items", [Headers=[Accept="application/json;odata=verbose"]])),
    d = Source[d],
    results = d[results],
    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Title", "Submitted", "SubmittedbyStringId", "Resolved", "Modified"}, {"Title", "Submitted", "SubmittedbyStringId", "Resolved", "Modified"})
in
    #"Expanded Column1"