Results 1 to 2 of 2

Thread: Looping through a table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Looping through a table

    I'm attempting to create a table to store all of my functions in. What I would like to do is have power query loop through this table and load in all the functions in said table, but I can't seem to find any way to make this happen. I assume there is a way, I just don't know enough yet about power query to make it happen. Does anyone know how to go about doing this, or can point me in the right direction?

  2. #2
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looping through a table

    Hi,

    Sorry, I am not good with the formatting on the message board. And the board will not allow me to upload an attachment.

    Below is a sample table called calcs that contains Field name, the formula and the type information.
    Field
    Expression
    Type
    sum
    List.Sum({[a],[b]})
    type number
    subtract
    [a]-[b]
    type number

    In your query that contains the data, add the boldfaced line.let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"a", Int64.Type}, {"b", Int64.Type}}),
    AddColumns = List.Accumulate(List.Buffer(Table.ToRecords(Calcs)), #"Changed Type",
    (s,c) => Table.AddColumn(s, c[Field], each Expression.Evaluate(c[Expression],[_=_] & #shared ), Expression.Evaluate(c[Type], #shared )))
    in
    AddColumns



    #"Change Type" would be one of your steps in your query. I have not tried this in a real transform yet so I do not know how well it performs. I would think adding the columns in your query would be more efficient. Are you looking to reduce the number of lines in your transform?

    Good luck and let me know how it works for you,
    Mike

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •