Results 1 to 3 of 3

Thread: Lopp through table like macro????
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2015
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Lopp through table like macro????

    I am trying to figure out if you can loop through data in the table in power query. Here is my scenario. I have employees who receive a bilingual bonus (BB). This amount gets charged to a cost centre for a specific time frame. The issue comes into play when the BB is charged to two or more CCs for the same time period. I need to be able to do a count of sorts so I can divide the cost evenly over the multiple cost centre for the specific time frame.

    I was hoping to create a conditional column with results that show the "Count" column as per below:

    CC EE # Start Date End Date BB Count
    120544320 1111111 2019-04-01 2019-06-26 Yes 1
    120544320 1111111 2019-06-27 2019-07-19 Yes 1
    120147200 9999999 2019-04-01 2019-05-03 Yes 3
    120147204 9999999 2019-04-01 2019-05-03 Yes 3
    120147203 9999999 2019-04-01 2019-05-03 Yes 3
    120147200 9999999 2019-05-04 2019-05-24 Yes 3
    120147203 9999999 2019-05-04 2019-05-24 Yes 3
    120147204 9999999 2019-05-04 2019-05-24 Yes 3
    120147203 9999999 2019-05-25 2019-07-19 Yes 2
    120147204 9999999 2019-05-25 2019-07-19 Yes 2
    Is this even possible in Power Query?

  2. #2
    Board Regular
    Join Date
    Nov 2017
    Posts
    72
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lopp through table like macro????

    try below
    Its adds a count for rows where EE# and Start Date are the same

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
       Ttl2 = Table.AddColumn(Source, "Count", (i) => List.Count(Table.SelectRows(Source, each ([#"EE #"] = i[#"EE #"] and [Start Date] = i[Start Date]))[#"EE #"]) , type number   )
    in Ttl2

  3. #3
    Board Regular
    Join Date
    Sep 2015
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lopp through table like macro????

    I will give it a try!!! Thanks!!!!

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
  •