SOLVED

Referencing A Text String (URL) In A Power Query

%3CLINGO-SUB%20id%3D%22lingo-sub-1637144%22%20slang%3D%22en-US%22%3EReferencing%20A%20Text%20String%20(URL)%20In%20A%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637144%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20Excel%20and%20Quick%20Base.%26nbsp%3B%20Quick%20Base%20allows%20querying%20data%20via%20URL.%20I%20have%20successfully%20set%20up%20and%20run%20a%20query%20in%20Excel%20Power%20Query%20from%20our%20Quick%20Base%20database.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20an%20example%20of%20what%20that%20Power%20Query%20looks%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elet%3CBR%20%2F%3ESource%20%3D%20Csv.Document(Web.Contents(%22%3CA%20href%3D%22https%3A%2F%2Fhardermech.quickbase.com%2Fdb%2Fjkii2h8%3Fact%3DAPI_GenResultsTable%26amp%3Busertoken%3Dusertoken%26amp%3Boptions%3Dcsv%26amp%3Bclist%3D8.3.6%26amp%3Bquery%3D%7B8.EX.410052%7DAND%7B29.EX.1%7DAND%7B31.XEX.6%7DAND%7B84.XEX.1%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fhardermech.quickbase.com%2Fdb%2Fjkii2h8%3Fact%3DAPI_GenResultsTable%26amp%3Busertoken%3Dusertoken%26amp%3Boptions%3Dcsv%26amp%3Bclist%3D8.3.6%26amp%3Bquery%3D%7B8.EX.410052%7DAND%7B29.EX.1%7DAND%7B31.XEX.6%7DAND%7B84.XEX.1%3C%2FA%3E%7D%22%3CBR%20%2F%3E)%2C%5BDelimiter%3D%22%2C%22%5D)%2C%3CBR%20%2F%3E%23%22Promoted%20Headers%22%20%3D%20Table.PromoteHeaders(Source%2C%20%5BPromoteAllScalars%3Dtrue%5D)%3CBR%20%2F%3Ein%3CBR%20%2F%3E%23%22Promoted%20Headers%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInstead%20of%20hard%20coding%20the%20entire%20text%20string%20for%20the%20URL%2C%20I%20want%20to%20build%20up%20the%20text%20string%20(parameterize%20it)%20in%20an%20Excel%20cell%20so%20that%20I%20can%20reference%20various%20values%20in%20the%20spreadsheet%2C%20then%20pull%20that%20string%20into%20the%20query%20source%20statement%20above.%3C%2FP%3E%3CP%3EI%20have%20found%20some%20online%20resources%20for%20building%20a%20Power%20Query%20to%20reference%20a%20specific%20cell%20on%20a%20spreadsheet.%3C%2FP%3E%3CP%3EHow%20do%20I%20then%20reference%20that%20parameter%20correctly%20in%20this%20Source%20statement%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1637144%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1637374%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%20A%20Text%20String%20(URL)%20In%20A%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637374%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F782696%22%20target%3D%22_blank%22%3E%40JonInPDX%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20that's%20one%20cell%20parameter%2C%20you%20may%20name%20such%20cell%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20432px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216713iF084BB199EB664D4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Equery%20it%20and%20use%20as%20parameter%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3Elet%0A%20%20%20%20GetURL%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22URLstring%22%5D%7D%5BContent%5D%5BColumn1%5D%7B0%7D%2C%0A%20%20%20%20Source%20%3D%20Csv.Document(Web.Contents(GetURL)%2C%5BDelimiter%3D%22%2C%22%5D)%2C%0A%20%20%20%20%23%22Promoted%20Headers%22%20%3D%20Table.PromoteHeaders(Source%2C%20%5BPromoteAllScalars%3Dtrue%5D)%0Ain%0A%20%20%20%20%23%22Promoted%20Headers%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20there%20are%20few%20parameters%2C%20idea%20is%20the%20same%20but%20you%20organize%20you%20parameter%20into%20Excel%20table%2C%20query%20it%2C%20combine%20value%20into%20one%20string%20and%20use%20to%20access%20web%20resource.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am using Excel and Quick Base.  Quick Base allows querying data via URL. I have successfully set up and run a query in Excel Power Query from our Quick Base database.

 

Here is an example of what that Power Query looks like:

 

let
Source = Csv.Document(Web.Contents("https://hardermech.quickbase.com/db/jkii2h8?act=API_GenResultsTable&usertoken=usertoken&options=csv&...}"
),[Delimiter=","]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"

 

Instead of hard coding the entire text string for the URL, I want to build up the text string (parameterize it) in an Excel cell so that I can reference various values in the spreadsheet, then pull that string into the query source statement above.

I have found some online resources for building a Power Query to reference a specific cell on a spreadsheet.

How do I then reference that parameter correctly in this Source statement?

3 Replies
Highlighted
Best Response confirmed by JonInPDX (New Contributor)
Solution

@JonInPDX 

If that's one cell parameter, you may name such cell

image.png

query it and use as parameter

let
    GetURL = Excel.CurrentWorkbook(){[Name="URLstring"]}[Content][Column1]{0},
    Source = Csv.Document(Web.Contents(GetURL),[Delimiter=","]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

If there are few parameters, idea is the same but you organize you parameter into Excel table, query it, combine value into one string and use to access web resource.

Highlighted

@Sergei Baklan Fantastic! That worked perfectly. Thank you!

Highlighted

@JonInPDX , you are welcome