locked
Feeding Multiple dynamic values into URL in Power query. RRS feed

  • Question

  • Hello,

    I recently was able to feed 1 dynamic value into power query, thanks to the Help of LZ on Microsoft Answers. Now I have similar task: I need to feed a column of values into it. How can I do it?

    If in column A I have a part numbers (inserted by user), link to intranet data soruce about this number will look something like this: CONCATENATE("//FirstPartOfURL";A1;"SecondPartOfURL"). Unfortunately you will not have access to our intranet to test it yourself =(. Ideally I want to get output in the same row, but that is not necessary, I can link to it later, via VLOOKUP.  

    Unfortunately forum does not allow me to insert links or images till my account verified. Hope it is understandable as it is.

    Best regards,

    Andrei.

    Monday, January 21, 2019 9:48 AM

Answers

  • Hi Andrej,

    for Ehren's solution, you'd simply add a column: "//FirstPartOfURL" & [A1] & "SecondPartOfURL"

    the ampersands are concanator-operators, so no need for a special function here.

    If that's not what you're after, please specify. Otherwise please mark as answer.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, February 3, 2019 6:34 PM

All replies

  • Update: Currently what I manage to be able to do is inserting multiple querries duplicating original one and appending one by one. It is not pretty, but it is working. Sure there is a better way...
    Thursday, January 24, 2019 2:01 PM
  • Hi Andrei

    Until the experts kill this I think I found a way to simulate (can't do a web query) your case

    Created 3 Tables (Table1, Table2, Table3) in an Excel workook. Image they represent what each of your URL returns. My 4th Table contains what I want to get and append (YOUR list of URLs)

    So above, with Table4 I want to get the content of Table1 and Table2 and finally append them...

    1/ Create a new blank query > Advanced Editor > Select all > Paste the following code that creates a function (the Following doesn't work - just to demo.)

    let myWebQuery = (myValue as text) =>   
        let
            Source = Excel.CurrentWorkbook(){[Name=myValue]}[Content],
    	Step2 = ...
    	Step3 = ...
        in
            Step3
    in
        myWebQuery

    2/ Replace the bolded steps with YOUR web query steps + replace the Bold-Italic "label" with the last one from your webquery + DO NOT hard-code any URL in that function ==> Use myValue instead

    3/ Close the Advance Editor > Rename that query i.e. myQuery:

    4/ Create a new blank query - the one that will get each value from my Table4 (your Table with each URL) and pass it to the function created at #3. Again Advanced Editor > Select all > Paste the following code

    EDIT: assumption made => each Table have the same number of columns. Otherwise following code requires adjustments

    let
        Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Table names", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "ToAppend",
            each myQuery([Table names]),
            type table
        ),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ToAppend"}),
        // Get the column names of the 1st (Lists start at index 0) nested Table
        // The list is then passed to the Expand step 
        ColumnNamesToExpand = Table.ColumnNames(#"Removed Other Columns"[ToAppend]{0}),
        #"Expanded ToAppend" = Table.ExpandTableColumn(#"Removed Other Columns", "ToAppend",
            ColumnNamesToExpand)    
    in
        #"Expanded ToAppend"

    You will need to adjust Table4 and [Table names] to your context. If I didn't make mistake and was clear enough that should do it. Below is my output (in PowerQ)

    Corresponding workbook avail. here

    Hope this helps...



    • Edited by Lz._ Friday, January 25, 2019 1:02 PM precision
    Thursday, January 24, 2019 5:25 PM
  • Hi Andrei. If your column of part numbers looks something like this:

    A
    P1
    P2
    P3

    What output are you wanting? Is it something like this?

    A     URL
    P1    //FirstPartOfURLP1SecondPartOfURL
    P2    //FirstPartOfURLP2SecondPartOfURL
    P3    //FirstPartOfURLP3SecondPartOfURL

    Ehren

    Thursday, January 24, 2019 7:53 PM
  • Hi Andrej,

    for Ehren's solution, you'd simply add a column: "//FirstPartOfURL" & [A1] & "SecondPartOfURL"

    the ampersands are concanator-operators, so no need for a special function here.

    If that's not what you're after, please specify. Otherwise please mark as answer.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, February 3, 2019 6:34 PM