Lopp through table like macro????

S

SAMCRO2014

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

[TABLE="width: 501"]
<colgroup><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]CC[/TD]
[TD]EE #[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]BB[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD="align: right"]120544320[/TD]
[TD="align: right"]1111111[/TD]
[TD="align: right"]2019-04-01[/TD]
[TD="align: right"]2019-06-26[/TD]
[TD]Yes[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]120544320[/TD]
[TD="align: right"]1111111[/TD]
[TD="align: right"]2019-06-27[/TD]
[TD="align: right"]2019-07-19[/TD]
[TD]Yes[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]120147200[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]2019-04-01[/TD]
[TD="align: right"]2019-05-03[/TD]
[TD]Yes[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]120147204[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]2019-04-01[/TD]
[TD="align: right"]2019-05-03[/TD]
[TD]Yes[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]120147203[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]2019-04-01[/TD]
[TD="align: right"]2019-05-03[/TD]
[TD]Yes[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]120147200[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]2019-05-04[/TD]
[TD="align: right"]2019-05-24[/TD]
[TD]Yes[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]120147203[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]2019-05-04[/TD]
[TD="align: right"]2019-05-24[/TD]
[TD]Yes[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]120147204[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]2019-05-04[/TD]
[TD="align: right"]2019-05-24[/TD]
[TD]Yes[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]120147203[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]2019-05-25[/TD]
[TD="align: right"]2019-07-19[/TD]
[TD]Yes[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]120147204[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]2019-05-25[/TD]
[TD="align: right"]2019-07-19[/TD]
[TD]Yes[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 398"]
<colgroup><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Is this even possible in Power Query?[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][TABLE="width: 398"]
<colgroup><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

jimrward
  • Solved
Process times
Replies
6
Views
440
JEC
JEC
M
  • Solved
Calculating two columsn in seperate tables with a dependency on another column
Replies
3
Views
567
Toadstool
Toadstool
C
  • Solved
Calculated field filtered tables
Replies
2
Views
576
comonand
C
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
553
etaf
etaf
muhammad susanto
  • Solved
Power Query : Transpose Column To Rows Then Fill Down Date in Other Column
Replies
5
Views
1K
alansidman
alansidman

Forum statistics

Threads
1,226,142
Messages
6,189,225
Members
453,531
Latest member
Dazzatron

Share this page

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