Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
johnlhaase
Helper I
Helper I

Count Occurrence with Index

Hello

 

I am working with a limited data set. I need to create a grouped index and my thought was to count each occurnace and fill down thereby giving me set or groups to work with.  In my table below I would like the top Duty to count as 1 and the next Duty would be 2. I could then fill or group each set of Duty, Block and Rest with 1, 1, 1 and 2, 2, 2 etc. I cannot figure this one out. The data is limiting and I do not want to spend time manully

 

FlightAssignmentHoursPICSICOver-20 PIC

3000Duty15120
3000Block13120
3000Rest46120
3000Duty18120
3000Block12120
3000Rest27120
3000Duty14120
3000Block12120
3000Rest22120

 

 

7 REPLIES 7
HotChilli
Super User
Super User

You've switched the requirements on me.  What's the desired output from that last sample data?

Hello the two original tables' data  lacks a useful key to join the two tables  tables. By using an idex based on the order of Duty, Block and Show Time I can then join the tables with an output I have below. Now I have some useful data. But without being able to increase the count for each Show Time it will not work, there could be two to three flights between the Show Times that need to be skiped.

Thanks

John Haase

 

PICSICOver-20 PICFlightDaysDayFlight2Hours3PIC4SIC5Fourth Pilot

3000Duty1512011Show Time12 
3000Block1312011Flight13121
3000Duty1812022Show Time12 
3000Block1212022K4 8078120
3000Duty1412033Show Time12 
3000Block1212033Flight12121
3000Duty612044Show Time12 
3000Block412044Flight4120
3000Duty1612055Show Time12 
3000Block1412055Flight14121
3000Duty2212166Show Time12 
3000Block1512066Flight8120
3000Duty1712077Show Time12 
3000Block1312077Flight12121

FlightDayFlightDaysAssignmentFlight2PICSICOver-20 PICHoursHours3

300011DutyShow Time12015 
300011BlockFlight1201313
300022DutyShow Time12018 
300022BlockFlight120128
300033DutyShow Time12014 
300033BlockFlight1201212
300044DutyShow Time1206 
300044BlockFlight12044
300055DutyShow Time12016 
300055BlockFlight1201414
300066DutyShow Time12122 
300066BlockFlight120158
300077DutyShow Time12017 
300077BlockFlight1201312

Hi  @johnlhaase,

 

Could you pls also provide your expected output based on your sample data?Best with your logic.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

johnlhaase
Helper I
Helper I

Hello Works great when we have a set number between each row. Now in this eaxmple beow there is no set number. The row sequence is the same "Show" then flights and "Debrief". There could be multiple flights. So, the set number is not always the same. The end Goal is to merge (join) the one set of days with the other. But your solution does with with a set number

 

DayFlightDeparture-Arrival

 Show Time 
15Jun TuK4 807CVG-BAH
 Debriefing 
 Show Time 
17Jun ThK4 807BAH-HKG
18Jun FrK4 860HKG-NGO
 Debriefing 
 Show Time 
19Jun SaK4 258NGO-LAX
 Debriefing 
 Show Time 
20Jun SuK4 232LAX-SYD
 Debriefing 
 Show Time 
24Jun ThK4 259SYD-SIN
 Debriefing 
 Show Time 
25Jun FrK4 259SIN-HKG
26Jun SaK4 258HKG-NGO
 Debriefing 
HotChilli
Super User
Super User

If you add an Index column (from 0) you can add another custom column like:

Number.IntegerDivide([Index], 3) + 1

and that should do it.  

I will try it now. Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors