none
How to "for each" from table to populate data associated with function call within same query. RRS feed

  • Question

  • Hello,

    I am new to Power Query (and this forum), have searched around for several days now, and am now am reaching out to this community for assistance. I am wanting to basically "for each" from values in the rows of a column. Here is the code with the part I need help on described within:

    let
        Source = Excel.CurrentWorkbook(){[Name="Filter"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"MyFilter", type text}}),
    
        Project = <Need code to pull all row values from the table "Project".  The table has one column with
    the header "MyFilter" in the first row. Each row value (not including header) should create a column in
    the next "WebData" call below where the function "fnQueryMy" will then populate the rows of each respective
    column.>
    WebData = Table.AddColumn(#"Changed Type", ""&Project&"", each fnQueryMy(Project, [MyFilter])) in WebData


    I am somewhat able to accomplish the above if I define "Project" as "Changed Type" and turn it into a function invoked from a separate query, however the problem with that method is that I have 720 rows in the "MyFilter" portion of the code and instead of ending up only having the added columns that I'm trying to accomplish with ""&Project&"" piece, I also end up with additional rows. So for example instead of ending up with 720 rows and 5 added columns (if there were 5 rows worth of values), I end up having to expand the data which in turn gives me 720*5 rows with the additional 5 columns.

    I also wanted to add for further clarification that the values in the rows I am wanting to iterate through are text based and the number of rows will change over time. Example row values:

    3-Way
    B2B
    Direct
    Straight Through Setup
    2.0 Testing

    I hope all of this sense makes sense and any assistance would be greatly appreciated!




    • Edited by Chaskel Monday, October 23, 2017 9:30 PM
    Monday, October 23, 2017 1:18 AM

Answers

  • Hi Chaskel. You should be able to do something like this:

    = List.Accumulate(Projects[MyProject], PreviousStepName, (myTable, currentProject) => Table.AddColumn(myTable, currentProject, each MyFunction(currentProject, [MyFilter])))

    See this thread for more examples of how to do this kind of thing.

    Ehren


    Wednesday, October 25, 2017 11:19 PM
    Owner
  • That's a lot more straightforward. You can just do a cross join followed by an expand to get all the Projects. Something like:

    = Table.NestedJoin(PreviousStepName,{},MyProjects,{},"Projects",JoinKind.FullOuter)

    (Note that PQ will think this step is incomplete, and thus will show you the previous step's result. To see the newly added column, click the fx button next to the formula bar to add a dummy step and it will show up.)

    Then expand the newly added column to pull out the project name.

    Finally, use Add Column -> Invoke Custom Function to invoke fnQueryWeb over each row of the table.

    No List.Accumulate needed!

    Ehren

    Friday, November 3, 2017 6:30 PM
    Owner
  • Hi Ehren,

    Thank you again! I had to make a slight modification to what you referenced as I believe the table/column names were reversed. Here is what I ended up with (modified for post to match references in thread above):

        Output = Table.NestedJoin(Source,{},Projects,{},"MyProject",JoinKind.FullOuter),
        Custom2 = Output,
        #"Expanded MyProject" = Table.ExpandTableColumn(Custom2, "MyProject", {"MyProject"}, {"MyProject.MyProject"}),
        #"Invoked Custom Function" = Table.AddColumn(#"Expanded MyProject", "Result", each fnQueryWeb([MyProject.MyProject], [MyFilter]))
    in
        #"Invoked Custom Function"

    Take care.

    • Marked as answer by Chaskel Tuesday, November 7, 2017 12:06 AM
    Tuesday, November 7, 2017 12:06 AM

All replies

  • Hi Chaskel. Can you reduce this to a simple example showing 3-5 rows of the table before your desired transform, and the same 3-5 rows after the transform is applied?

    That will help us point you in the right direction.

    Ehren

    Monday, October 23, 2017 7:36 PM
    Owner
  • Hi Ehren,

    Many thanks for your message. Hopefully the following is what you are looking for and will help. Please note I've needed to substitute some values to make it visible for a wider audience:

    Table "Projects" in its own worksheet (number of rows will change over time and each value is manually defined by me in the table):

    MyProject
    3-Way
    B2B
    Direct
    Straight Through Setup
    2.0 Testing

    Table "Filters" in its own worksheet (720 rows and am only listing a few examples). Note for the "MyFilter" column, it is doing a text join of the previous columns to come up with the value for each row:

    Operator1 P Operator2 S1 Operator3 S2 Operator4 T Operator5 F MyFilter
    AND 1 AND (Level 1) AND Changed AND Type AND Production AND P= 1 AND S (Level 1) AND Changed AND Type is Type AND Found in Production
    AND 1 AND (Level 1) AND Changed AND Type AND NOT Production AND P= 1 AND S (Level 1) AND Changed AND Type is Type AND NOT Found in Production
    AND 1 AND (Level 1) AND Changed AND NOT Type AND Production AND P= 1 AND S (Level 1) AND Changed AND NOT Type is Type AND Found in Production

    Desired output from query into new worksheet (values in the added columns populated by web call):

    Operator1 P Operator2 S1 Operator3 S2 Operator4 T Operator5 F MyFilter 3-Way B2B Direct Straight Through Setup 2.0 Testing
    AND 1 AND (Level 1) AND Changed AND Type AND Production AND P= 1 AND S (Level 1) AND Changed AND Type is Type AND Found in Production 1 0 0 1 0
    AND 1 AND (Level 1) AND Changed AND Type AND NOT Production AND P= 1 AND S (Level 1) AND Changed AND Type is Type AND NOT Found in Production 0 1 0 0 0
    AND 1 AND (Level 1) AND Changed AND NOT Type AND Production AND P= 1 AND S (Level 1) AND Changed AND NOT Type is Type AND Found in Production 0 1 1 1 0

    Example of a function I've used to populate the URL to make the call to return the values in the added columns (where MyProject below is a row value from the "Projects" table and "MyFilter" is from each row in the Filters table):

    (MyProject as text, MyFilter as text) =>

    let
            url = "https://mydomain.com/rest/search?Project%20=%20" & MyProject & "%20" & MyFilter & "&max=10",
            auth = [Headers=[Authorization="Basic <removed>"]],
            Source = Json.Document(Web.Contents(url,auth)),

            #"Converted to Table" = Record.ToTable(Source),
            value1 = #"Converted to Table"{2}[Value]
    in
        value1

    Thank you!

    P.S. I'm not sure if the table width for the above examples displays everything for you in the post. If not if you copy the table and paste it into excel it should display everything. (not sure if there is a better way to do on the forum)
    • Edited by Chaskel Monday, October 23, 2017 10:07 PM
    Monday, October 23, 2017 10:05 PM
  • Hi Chaskel. You should be able to do something like this:

    = List.Accumulate(Projects[MyProject], PreviousStepName, (myTable, currentProject) => Table.AddColumn(myTable, currentProject, each MyFunction(currentProject, [MyFilter])))

    See this thread for more examples of how to do this kind of thing.

    Ehren


    Wednesday, October 25, 2017 11:19 PM
    Owner
  • Hi Ehren,

    Thank you for the information. Please forgive me as I suspect I am missing something very basic in the example code you provided (and I'm not necessarily following what is in the thread you linked to (seems  a little more complex than the approach you listed)).

    I am trying the following and I receive "Expression.Error: The name 'Projects' wasn't recognized. Make sure it's spelled correctly"

    This is the query and please note that if I am supposed to be substituting "myTable" and "currentProject" with something else please let me know:

      

    let
        Source = Excel.CurrentWorkbook(),

        Output = List.Accumulate(Projects[MyProject], Source, (myTable, currentProject) => Table.AddColumn(myTable, currentProject, each fnQueryWeb(currentProject, [MyFilter])))

    in
        Output

    Please note that I am using the table and column names listed in the example I posted previously while trying to test this. I even tried renaming the "Projects" table to something else and then reference it with the new name but then the Expression.Error message changed from being "Projects" to being the new value I entered (I am paying attention to case sensitivity).

    fnQueryWeb matches up to the code provided in my earlier posting except that I changed "MyProject" in the function to be "currentProject" to match what you listed.

    Thank you

    Thursday, October 26, 2017 1:35 AM
  • Hi Chaskel. I'd recommend you do the following:

    • Select your Projects table (in Excel, not Power Query), and click the PQ "From Table/Range" button. This will import it into PQ, and allow your other queries to "see" it.
    • Name this imported query "Projects".
    • Select your Filters table (in Excel, not PQ), and click "From Table/Range".
    • Click the fx button next to the formula bar, and paste the custom formula I provided. Modify it to refer to the previous step of the current query.

    Let me know if that works.

    Ehren


    Thursday, October 26, 2017 4:54 PM
    Owner
  • Hi Ehren,

    I'll try this right now and will get back to you shortly.

    Thank you!

    Thursday, October 26, 2017 5:06 PM
  • Ehren - you are awesome! The initial preview seems to be good (it took a while to pull the query). One thing I did have to do however that is not listed is (and I'm not sure how bad this is but I did it for "Current Workbook as opposed to "Global) is to select "Ignore the Privacy Levels and potentially improve performance"(in the Query Editor -> File -> Options and settings -> Query Options)

    Unless I run into another issue I think I'm good and if that's the case will come back later and mark what you mention as the answer.

    Thank you again!

     
    • Marked as answer by Chaskel Friday, October 27, 2017 12:01 AM
    • Unmarked as answer by Chaskel Thursday, November 2, 2017 11:22 PM
    Thursday, October 26, 2017 5:23 PM
  • Hi Ehren,

    I have one more question if possible. Using the similar example data above, how would I modify what you listed so that I add 2 columns with one being the projects (which iterates through MyFilter for each) and then the other being the values returned from the fnQueryWeb:

    

    So in the above it has iterated through "MyFilter' (3 rows) for each of the 5 projects for a total of 15 rows.

    Thank you

    Thursday, November 2, 2017 11:35 PM
  • That's a lot more straightforward. You can just do a cross join followed by an expand to get all the Projects. Something like:

    = Table.NestedJoin(PreviousStepName,{},MyProjects,{},"Projects",JoinKind.FullOuter)

    (Note that PQ will think this step is incomplete, and thus will show you the previous step's result. To see the newly added column, click the fx button next to the formula bar to add a dummy step and it will show up.)

    Then expand the newly added column to pull out the project name.

    Finally, use Add Column -> Invoke Custom Function to invoke fnQueryWeb over each row of the table.

    No List.Accumulate needed!

    Ehren

    Friday, November 3, 2017 6:30 PM
    Owner
  • Hi Ehren,

    Thank you again! I had to make a slight modification to what you referenced as I believe the table/column names were reversed. Here is what I ended up with (modified for post to match references in thread above):

        Output = Table.NestedJoin(Source,{},Projects,{},"MyProject",JoinKind.FullOuter),
        Custom2 = Output,
        #"Expanded MyProject" = Table.ExpandTableColumn(Custom2, "MyProject", {"MyProject"}, {"MyProject.MyProject"}),
        #"Invoked Custom Function" = Table.AddColumn(#"Expanded MyProject", "Result", each fnQueryWeb([MyProject.MyProject], [MyFilter]))
    in
        #"Invoked Custom Function"

    Take care.

    • Marked as answer by Chaskel Tuesday, November 7, 2017 12:06 AM
    Tuesday, November 7, 2017 12:06 AM
  • Great! Glad it worked for you.

    Ehren

    Tuesday, November 7, 2017 12:08 AM
    Owner