locked
Run Powerquery dynamically based on cell value RRS feed

  • Question

  • I have a spreadsheet that has a column with six digit numbers.  I am trying to run the six digit numbers through a web API.  The API is in the URL format:  https://binlist.net/csv/{6_DIGIT_NUMBER}.

    For example:  https://binlist.net/csv/455555 returns:

    455555,VISA,,DE,Germany,GE MONEY BANK,CREDIT,,51,9

    I was able to use PowerQuery and enter the sample URL, and it makes a great row with the data.

    My goal is to take my existing column with six digit numbers, and dynamically run it through the API and display the data on the same row. For example:

    In A2, I type 455555. Output is in A3-A11 or whatever it might be.

    Is this possible?

    All I have so far is:

    let    Source = Csv.Document(Web.Contents("https://binlist.net/csv/455555"),[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", Int64.Type}, {"Column10", Int64.Type}})in    #"Changed Type"

    Thank you for any help/guidance you can provide!

    Wednesday, December 14, 2016 5:53 PM

Answers