Lopp through table like macro????

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
158
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:

CCEE #Start DateEnd DateBBCount
12054432011111112019-04-012019-06-26Yes1
12054432011111112019-06-272019-07-19Yes1
12014720099999992019-04-012019-05-03Yes3
12014720499999992019-04-012019-05-03Yes3
12014720399999992019-04-012019-05-03Yes3
12014720099999992019-05-042019-05-24Yes3
12014720399999992019-05-042019-05-24Yes3
12014720499999992019-05-042019-05-24Yes3
12014720399999992019-05-252019-07-19Yes2
12014720499999992019-05-252019-07-19Yes2

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>
Is this even possible in Power Query?

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top