let
// Get the values of parameters
User_ID = Text.From(#"User_ID"),
Bearer_Key = Text.From(#"Bearer_Key"),
ItemsPerPage = Text.From(#"ItemsPerPage"),
ABS_Server = Text.From(#"ABS_Server"),
// Construct the API URL
apiUrl = "https://" & ABS_Server & "/api/users/" & User_ID & "/listening-sessions?itemsPerPage=" & ItemsPerPage,
// Set the headers with the Bearer Token
headers = [#"Authorization" = "Bearer " & Bearer_Key],
// Make the GET request
response = Web.Contents(apiUrl, [Headers=headers]),
// Convert the response to JSON
jsonResponse = Json.Document(response),
// Extract 'sessions' field
sessions = jsonResponse[sessions],
// Convert 'sessions' list to a table
sessionsTable = Table.FromList(sessions, Splitter.SplitByNothing()),
// Expand the record fields
expandedFields = Table.ExpandRecordColumn(sessionsTable, "Column1", {"id", "userId", "libraryId", "libraryItemId", "bookId", "episodeId", "mediaType", "mediaMetadata", "chapters", "displayTitle",
"displayAuthor", "coverPath", "duration", "playMethod", "mediaPlayer", "deviceInfo", "serverVersion", "date", "dayOfWeek", "timeListening", "startTime", "currentTime", "startedAt", "updatedAt"}),
// Expand the 'mediaMetadata' record
expandedMediaMetadata = Table.ExpandRecordColumn(expandedFields, "mediaMetadata", {"title", "subtitle", "author", "releaseDate"}),
// Convert duration to HH:MM:SS format
durationFormatted = Table.AddColumn(expandedMediaMetadata, "Duration (HH:MM:SS)", each
let
totalSeconds = [duration],
hours = Number.RoundDown(totalSeconds / 3600),
remainingSeconds = totalSeconds - hours * 3600,
minutes = Number.RoundDown(remainingSeconds / 60),
seconds = Number.RoundDown(remainingSeconds - minutes * 60),
formattedDuration = Text.From(hours) & ":" & Text.From(minutes) & ":" & Text.From(seconds)
in
formattedDuration
),
// Convert date to DateTime type first, and then to UK format
dateFormatted = Table.TransformColumns(durationFormatted, {{"date", each DateTime.FromText(_, "en-GB"), type datetime}}),
dateFormattedUK = Table.TransformColumns(dateFormatted, {{"date", each DateTime.ToText(_, "dd/MM/yyyy"), type text}}),
// Select the desired fields
selectedFields = Table.SelectColumns(dateFormattedUK, {"mediaType", "title", "subtitle","author", "displayTitle", "displayAuthor", "Duration (HH:MM:SS)", "date", "dayOfWeek", "timeListening"}),
// Remove wrong column
#"Removed Columns" = Table.RemoveColumns(selectedFields,{"author"}),
//Rename Columns to make it easier
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"displayTitle", "episode Title or Book Title"}, {"displayAuthor", "Author"}, {"date", "Date Listened"}, {"Duration (HH:MM:SS)", "Duration of Title (HH:MM:SS)"}, {"timeListening", "Listening Time (Seconds)"}}),
// Convert Listening Time to HH:MM:SS format
listeningTimeFormatted = Table.AddColumn(#"Renamed Columns", "Listening Time (HH:MM:SS)", each
let
totalSeconds = [#"Listening Time (Seconds)"],
hours = Number.RoundDown(totalSeconds / 3600),
remainingSeconds = totalSeconds - hours * 3600,
minutes = Number.RoundDown(remainingSeconds / 60),
seconds = Number.Round(remainingSeconds - minutes * 60, 0),
formattedListeningTime = Text.From(hours) & ":" & Text.From(minutes) & ":" & Text.From(seconds)
in
formattedListeningTime
),
//Convert Date Listened to Date Format
#"Changed Type" = Table.TransformColumnTypes(listeningTimeFormatted,{{"Date Listened", type date}, {"Duration of Title (HH:MM:SS)", type time}, {"Listening Time (HH:MM:SS)", type time}})
in
#"Changed Type"