Results of query 1 as input to query 2

[alfordtp]

I've seen a few post that are similar but nothing seems to fix my issues.

Using the last.fm api:  enter 1 artist, return 100 similar artists.  Use those 100 artists as input and find their similar artists.  In the end, 10,000 records.

let
    Source = Json.Document(Web.Contents("http://ws.audioscrobbler.com/2.0/?method=artist.getsimilar&artist=" & ArtistName1 & "&api_key=APIKEY&format=json")),
    similarartists = Source[similarartists],
    artist = similarartists[artist],
    #"Converted to Table" = Table.FromList(artist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"name", "mbid", "match", "url", "image", "streamable"}, {"Column1.name", "Column1.mbid", "Column1.match", "Column1.url", "Column1.image", "Column1.streamable"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.mbid", "Column1.image", "Column1.streamable", "Column1.url"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.match", "%Match"}, {"Column1.name", "RelatedArtist"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Original Artist", each ArtistName1)
in
    #"Added Custom"

Not sure how many queries I need.  I've tried to "Invoke custom query" as a second column where the first column contains the 100 related artists, but that isnt working for me.

Any thoughts, posts, references, or tutorials would be appreciated.

[jeroenterheerdt · Accepted solution]

I would just wrap the call to `artists.getsimilar` in the API in a function that takes the `ArtistName` as a parameter.

Something like this is what you would end up with: 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsrPycnMS1cILsnPSy1Wio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Artist = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Artist", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "RelatedArtists", each GetRelated([Artist])),
#"Expanded RelatedArtists" = Table.ExpandTableColumn(#"Invoked Custom Function", "RelatedArtists", {"RelatedArtist", "%Match"}, {"RelatedArtist", "%Match"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded RelatedArtists",{{"%Match", "First%Match"}, {"RelatedArtist", "FirstRelatedArtist"}}),
#"Invoked Custom Function1" = Table.AddColumn(#"Renamed Columns", "SecondRelatedArtist", each GetRelated([FirstRelatedArtist])),
#"Expanded SecondRelatedArtist" = Table.ExpandTableColumn(#"Invoked Custom Function1", "SecondRelatedArtist", {"RelatedArtist", "%Match"}, {"SecondRelatedArtist.RelatedArtist", "SecondRelatedArtist.%Match"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded SecondRelatedArtist",{{"SecondRelatedArtist.RelatedArtist", "SecondRelatedArtist"}, {"SecondRelatedArtist.%Match", "Second%Match"}})
in
#"Renamed Columns1"

This calls the custom function below twice - once to get the first "ring" of related artists and their match%, passing in the artist what was originally in the table (just Rolling Stones in my example, but you can have more artists in the original table to do this for more than one artist), and once more to get the second "ring" of related artists and their match% passing in each artist from the first "ring".

The function:

let
Source = Json.Document(Web.Contents("http://ws.audioscrobbler.com/2.0/?method=artist.getsimilar&artist=" & Artist & "&api_key=" & APIKEY & "&format=json")),
similarartists = Source[similarartists],
artist = similarartists[artist],
#"Converted to Table" = Table.FromList(artist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"name", "mbid", "match", "url", "image", "streamable"}, {"Column1.name", "Column1.mbid", "Column1.match", "Column1.url", "Column1.image", "Column1.streamable"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.mbid", "Column1.image", "Column1.streamable", "Column1.url"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.match", "%Match"}, {"Column1.name", "RelatedArtist"}})
in
#"Renamed Columns"

The result:

and so on...

[alfordtp]

This mostly works!  However, I get an error when I try to apply this:

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] The column 'Column1' of the table wasn't found.. '.

Any ideas why I would get this?

[jeroenterheerdt]

that is probably because there might be empty results. Let me see if I can come up with some solution here.

[jeroenterheerdt]

hmm, there is a null value somewhere, but I cannot really figure out where. I would recommend going through it step by step and building in logic to handle the edge cases where you get no results.

[alfordtp]

Thank you for your help so far.  Can you point me to some examples of this type of error handling?

[jeroenterheerdt]

https://docs.microsoft.com/en-us/powerquery-m/m-spec-error-handling