Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I've connected PowerBI to the Spotify API. I input an artist, and the query returns the related artists with their top tracks. I'm filtering it down and want to use the POST request option in Spotify to add these tracks to a playlist.
I've tried setting up a function with the POST code and then "Invoke Custom Function", but that doesnt add the tracks. I've tried adding the code directly to the original query, but that doesnt add the tracks. Not sure what I should be doing to get this to work.
let
Source = Json.Document(Web.Contents("https://api.spotify.com/v1/search?q=" & q & "&type=artist&market=us&limit=10", [Headers=[Accept="application/json", #"Content-Type"="application/json", Authorization= "Bearer xxx" ]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded artists" = Table.ExpandRecordColumn(#"Converted to Table", "artists", {"href", "items", "limit", "next", "offset", "previous", "total"}, {"artists.href", "artists.items", "artists.limit", "artists.next", "artists.offset", "artists.previous", "artists.total"}),
#"Expanded artists.items" = Table.ExpandListColumn(#"Expanded artists", "artists.items"),
#"Expanded artists.items1" = Table.ExpandRecordColumn(#"Expanded artists.items", "artists.items", {"external_urls", "followers", "genres", "href", "id", "images", "name", "popularity", "type", "uri"}, {"artists.items.external_urls", "artists.items.followers", "artists.items.genres", "artists.items.href", "artists.items.id", "artists.items.images", "artists.items.name", "artists.items.popularity", "artists.items.type", "artists.items.uri"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded artists.items1",{"artists.href", "artists.items.external_urls", "artists.items.followers", "artists.items.genres", "artists.items.href", "artists.items.images", "artists.items.type", "artists.items.uri", "artists.limit", "artists.next", "artists.offset", "artists.previous", "artists.total"}),
#"Kept First Rows" = Table.FirstN(#"Removed Columns",1),
#"Invoked Custom Function" = Table.AddColumn(#"Kept First Rows", "RelatedArts", each RelatedArts([artists.items.id], null)),
#"Expanded RelatedArts" = Table.ExpandTableColumn(#"Invoked Custom Function", "RelatedArts", {"artists.name", "artists.id", "artists.followers.total", "artists.popularity"}, {"RelatedArts.artists.name", "RelatedArts.artists.id", "RelatedArts.artists.followers.total", "RelatedArts.artists.popularity"}),
#"Invoked Custom Function1" = Table.AddColumn(#"Expanded RelatedArts", "RelatedArts", each RelatedArts([RelatedArts.artists.id], null)),
#"Expanded RelatedArts1" = Table.ExpandTableColumn(#"Invoked Custom Function1", "RelatedArts", {"artists.name", "artists.id", "artists.followers.total", "artists.popularity"}, {"RelatedArts.artists.name.1", "RelatedArts.artists.id.1", "RelatedArts.artists.followers.total.1", "RelatedArts.artists.popularity.1"}),
#"Grouped Rows" = Table.Group(#"Expanded RelatedArts1", {"RelatedArts.artists.name.1", "RelatedArts.artists.id.1"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Descending}}),
#"Kept First Rows1" = Table.FirstN(#"Sorted Rows",25),
#"Removed Columns1" = Table.RemoveColumns(#"Kept First Rows1",{"Count"}),
#"Invoked Custom Function2" = Table.AddColumn(#"Removed Columns1", "TopTracks", each TopTracks([RelatedArts.artists.id.1], null)),
#"Expanded TopTracks" = Table.ExpandTableColumn(#"Invoked Custom Function2", "TopTracks", {"tracks.id", "tracks.name", "tracks.popularity"}, {"TopTracks.tracks.id", "TopTracks.tracks.name", "TopTracks.tracks.popularity"}),
#"Grouped Rows1" = Table.Group(#"Expanded TopTracks", {"RelatedArts.artists.name.1"}, {{"AllRows", each _, type table [RelatedArts.artists.name.1=text, RelatedArts.artists.id.1=text, TopTracks.tracks.id=text, TopTracks.tracks.name=text, TopTracks.tracks.popularity=number]}}),
//Declare a function that adds a Rank column to a table
RankFunction = (tabletorank as table) as table =>
let
SortRows = Table.Sort(tabletorank,{{"TopTracks.tracks.popularity", Order.Descending}}),
AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1)
in
AddIndex,
//Apply that function to the AllRows column
AddedRank = Table.TransformColumns(#"Grouped Rows1", {"AllRows", each RankFunction(_)}),
#"Expanded AllRows" = Table.ExpandTableColumn(AddedRank, "AllRows", {"RelatedArts.artists.name.1", "RelatedArts.artists.id.1", "TopTracks.tracks.id", "TopTracks.tracks.name", "TopTracks.tracks.popularity", "Rank"}, {"AllRows.RelatedArts.artists.name.1", "AllRows.RelatedArts.artists.id.1", "AllRows.TopTracks.tracks.id", "AllRows.TopTracks.tracks.name", "AllRows.TopTracks.tracks.popularity", "AllRows.Rank"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded AllRows", each [AllRows.Rank] <= 3)
in
#"Filtered Rows"
"Bearer xxx", where xxx = personal OAuth key from my account
The above code returns a table with records. One of the columns has the track ids that I need to pass on to the step of adding the tracks to the specified playlist.
The POST example is as follows:
curl -X "POST" "https://api.spotify.com/v1/playlists/{playlistID}/tracks?uris=spotify%3Atrack%{trackid1}%2Cspotify%3Atrack%{trackid2}" -H "Accept: application/json" -H "Content-Type: application/json" -H "Authorization: Bearer xxx"
Any thoughts?
I don't see any actual POST requests in your code. Is that another one of these weird "POST without payload" scenarios? In order to make Web.Contents() do a POST you need to provide some payload in the Content option section.
Web.Contents - PowerQuery M | Microsoft Docs
at a minimum provide an empty string as payload.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |