Power query - Use a column as url for API call

%3CLINGO-SUB%20id%3D%22lingo-sub-2281271%22%20slang%3D%22en-US%22%3EPower%20query%20-%20Use%20a%20column%20as%20url%20for%20API%20call%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281271%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3EFirst%20of%20all%2C%20thank%20you%20for%20your%20time%20in%20helping%20people%20like%20me%20who%20need%20help.%3C%2FP%3E%3CP%3EI%20have%20been%20banging%20my%20head%20on%20my%20desk%20since%20monday%20on%20this%20issue.%20I%20am%20an%20author%20on%20a%20website%20that%20write%20novels%20and%20I%20built%20a%20dashboard%20with%20the%20Excel%20Power%20Query%20tool.%3C%2FP%3E%3CP%3EThere%20is%20still%20one%20thing%20I%20can't%20do%20though%2C%26nbsp%3BI%20am%20trying%20to%20use%20a%20text%20string%20contained%20in%20a%20column%20as%20argument%20to%20make%20an%20API%20call.%3C%2FP%3E%3CP%3EThe%20function%20is%20the%20following%20%3A%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Silviooooooo_0-1619078862866.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274641i06F0B03B93D92FE8%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Silviooooooo_0-1619078862866.png%22%20alt%3D%22Silviooooooo_0-1619078862866.png%22%20%2F%3E%3C%2FSPAN%3E%3CP%3EWhen%20I%20use%20it%20with%20a%20direct%20text%20string%2C%20it%20works%20like%20a%20charm%20as%20you%20can%20see%20in%20the%20%22Personnalis%C3%A9%22%20column%20below%3A%3C%2FP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Silviooooooo_1-1619078880459.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274642i98BF24F18A5120EC%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Silviooooooo_1-1619078880459.png%22%20alt%3D%22Silviooooooo_1-1619078880459.png%22%20%2F%3E%3C%2FSPAN%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Silviooooooo_2-1619078887905.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274643iB1CA324313E66A5C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Silviooooooo_2-1619078887905.png%22%20alt%3D%22Silviooooooo_2-1619078887905.png%22%20%2F%3E%3C%2FSPAN%3E%3CP%3EThe%20only%20issue%20is%20that%20in%20each%20line%20of%20my%20table%2C%20i%20want%20to%20use%20a%20different%20argument%20(%3D%20use%20a%20different%20url%20for%20my%20API%20request).%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20to%20use%20a%20column%20as%20argument%2C%20but%20after%20several%20days%20of%20hardship%2C%20i%20come%20here%20to%20ask%20your%20help.%20Here%20is%20what%20I%20get%20so%20far%E2%80%A6%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Silviooooooo_3-1619078911292.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274644i48E1346248F615F3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Silviooooooo_3-1619078911292.png%22%20alt%3D%22Silviooooooo_3-1619078911292.png%22%20%2F%3E%3C%2FSPAN%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Silviooooooo_4-1619078915444.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274645iBFB35BA5BD82B97B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Silviooooooo_4-1619078915444.png%22%20alt%3D%22Silviooooooo_4-1619078915444.png%22%20%2F%3E%3C%2FSPAN%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20all%20very%20much%20for%20your%20help.%3CBR%20%2F%3EHave%20a%20great%20day%20!%3CBR%20%2F%3ESilvio%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2281271%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2281344%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20query%20-%20Use%20a%20column%20as%20url%20for%20API%20call%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281344%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3BThank%20you%20for%20your%20suggestion%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%3CBR%20%2F%3EI%20tried%20to%20use%20the%20RelativePath%20parameter%20in%20my%20query%2C%20as%20shown%20in%20the%20blog%20you%20provided%20but%20I%20had%20no%20luck.%20The%20function%20i%20used%20was%20the%20below%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Silviooooooo_0-1619084600956.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274667iEDE2056253450F8B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Silviooooooo_0-1619084600956.png%22%20alt%3D%22Silviooooooo_0-1619084600956.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHowever%20this%20still%20returns%20this%20error%20message%20when%20i%20try%20to%20give%20the%20Column%20as%20argument%20in%20the%20RelativePath%20option%20%3A%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Silviooooooo_1-1619084629940.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274668i2AED3A9306FBC2CC%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Silviooooooo_1-1619084629940.png%22%20alt%3D%22Silviooooooo_1-1619084629940.png%22%20%2F%3E%3C%2FSPAN%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20it%20be%20related%20to%20the%20fact%20that%20the%20error%20message%20indicates%20that%20the%20%22%20character%20are%20replaced%20by%20%2522%20in%20the%20url%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2281288%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20query%20-%20Use%20a%20column%20as%20url%20for%20API%20call%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281288%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1033499%22%20target%3D%22_blank%22%3E%40Silviooooooo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can't%20combine%20dynamic%20parameter%20directly%20with%20URL%20string.%20Use%20RelativePart%20parameter%20instead%2C%20it's%20explained%20for%20example%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fblog.crossjoin.co.uk%2F2016%2F08%2F16%2Fusing-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fblog.crossjoin.co.uk%2F2016%2F08%2F16%2Fusing-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello all,

First of all, thank you for your time in helping people like me who need help.

I have been banging my head on my desk since monday on this issue. I am an author on a website that write novels and I built a dashboard with the Excel Power Query tool.

There is still one thing I can't do though, I am trying to use a text string contained in a column as argument to make an API call.

The function is the following :

Silviooooooo_0-1619078862866.png

When I use it with a direct text string, it works like a charm as you can see in the "Personnalisé" column below:

Silviooooooo_1-1619078880459.png
Silviooooooo_2-1619078887905.png

The only issue is that in each line of my table, i want to use a different argument (= use a different url for my API request).

 

I tried to use a column as argument, but after several days of hardship, i come here to ask your help. Here is what I get so far…

Silviooooooo_3-1619078911292.png
Silviooooooo_4-1619078915444.png

 

Thank you all very much for your help.
Have a great day !
Silvio

 

2 Replies

@Silviooooooo 

You can't combine dynamic parameter directly with URL string. Use RelativePart parameter instead, it's explained for example here https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

 Thank you for your suggestion @Sergei Baklan,
I tried to use the RelativePath parameter in my query, as shown in the blog you provided but I had no luck. The function i used was the below:

Silviooooooo_0-1619084600956.png

However this still returns this error message when i try to give the Column as argument in the RelativePath option :

Silviooooooo_1-1619084629940.png

 

Could it be related to the fact that the error message indicates that the " character are replaced by %22 in the url ?