none
Implementing a loop against a table to execute an action for each retrieved row RRS feed

  • Question

  • Hi,

    in Power Query I'd like to implement a loop to execute an action respect to each row read from the connected data source.

    Is it possible? How?

    Many thanks

    Thursday, February 2, 2017 10:39 PM

Answers

  • No, as explained earlier, you can't output multiple tables from 1 query.

    And the name of a table is not a property of the table itself, but rather a step name (inside a query) or a query name (outside the query).

    In this post you can find an example of using recursive functions.

    Friday, February 3, 2017 2:40 PM

All replies

  • It depends on the requirements, e.g. if you want to add a number to some field in each row, then you can just add a column in which the number is added to the field.

    For cumulative values, something similar is possible, but the formula will be a bit more complicated.

    For some situations, you might use Fill Down or Fill Up or Group By or some other function from which the result is similar to what you would get from looping in another language.

    If you have a situation in which the number of iterations depends on a condition that can only be determined during iteration, I know 2 alternatives: the function List.Generate or using a recursive function.

    If the overall result of your action will be 1 number, List.Accumulate might be what you're looking for.

    Also the available options may be limited if you connect to your data source directly (Direct Query), otherwise I have no experience with Direct Query.

    My personal view on looping in Power Query is that actual looping (with Lust.Generate or a nested function), is not often required and is also not very "Power Query like": most of the times an alternative solution will be better.

    If you need a more specific answer for your situation, then please provide more detailed information.

    Friday, February 3, 2017 2:32 AM
  • Hi Marcel,

    thanks for your reply.

    I try to explain better what I'd like to do.

    1. I need to connect a data source like a SQL Server table or odata file to read the contained data. This table or file reports, for each row, in a column (or field) the name of the table that I need to create.

    2. Then, I need to scan the table or the file row by row in order to create a table to name e.g. as "DIM_" + number of the fetched row. In a column of the table to create I need to save the retrieved name of the table.

    For example, I need to connet to this table:

    column 1      column 2   ...

    PRODUCTS   ...

    VENDORS     ...

    and I want to create this two tables (because I've read two rows):

    DIM_01

    column 1      column 2   ...

    PRODUCTS   ...

    DIM_02

    column 1      column 2   ...

    VENDORS   ...

    I need to use Power Query to create dinamically dim tables in a Power BI data model.

    Many thanks

    Friday, February 3, 2017 6:51 AM
  • That is not possible. You may want to vote for this idea.
    Friday, February 3, 2017 7:39 AM
  • Ok thanks, but I hope to explain better the issue.

    A loop mechanism in Power Query, like a while, a for each or a SQL cursor, it could be more useful.

    Thanks

    Friday, February 3, 2017 9:57 AM
  • As already explained, you can use List.Generate or a recursive function to create a (virtual) loop within a query, like DO..WHILE.

    For example I have a query in which I use a recursive function to add a dynamic number of columns to a table.

    But it is not possible to generate multiple tables that can be accessed outside your query.
    (Inside a query, you can generate multiple tables and reference them).

    So again, my suggestion is to vote for the idea I linked in my previous reply.
    You can also add comments to existing ideas.
    If that's not what you are looking for, you can raise a new idea.

    Friday, February 3, 2017 11:02 AM
  • Hi Marcel,

    again thanks for your reply.

    I've already to add a comment to the suggested idea in the Power BI forum.

    Well, I'd like to understand better how implementing the suggested virtual loop.

    In order to represent my scenario, in this case is it possible to apply the suggested virtual loop?


    • Edited by pscorca Friday, February 3, 2017 11:19 AM
    Friday, February 3, 2017 11:14 AM
  • Tables can be embedded within a column of a query table and the columns can be expanded. However, they cannot be separated into individual queries - except manually. 
    Friday, February 3, 2017 2:35 PM
  • No, as explained earlier, you can't output multiple tables from 1 query.

    And the name of a table is not a property of the table itself, but rather a step name (inside a query) or a query name (outside the query).

    In this post you can find an example of using recursive functions.

    Friday, February 3, 2017 2:40 PM
  • Ok Marcel, so I cannot solve the issue thinking a recursive function in 1 query that creates a new table whenever it is invoked.

    It is a pity that it isn't possible to create a table parameterizing the related name.

    Thanks

    Friday, February 3, 2017 2:57 PM