Having trouble with transforming data from a table with similar multiple columns

[rksharma86]

Background: I have a table which contains the code breach incidents of banks. Each line represents a incident, along with cause of incident, identification, remediation and corrective action, customer impact and financial impact as below.

Bank

Ref_Key(PK)

Incident Description

Cause

Identification Method

Remediation

Corrective Action

Customer Impact

Financial impact

Code chapter breached (1)

No of breaches of Code chapter (1)

Code chapter breached (2)

Number of breaches of code chapter (2)

Code chapter breached (3)

Number of breaches of code chapter (3)

Bank A

1

Free text field(abc, xyz)

Human

Complaints

Reimbursment

Coaching

1

500

Chapter 2

1

Chapter 10

1

Bank A

2

Free text field(abc, xyz)

System

Customer

Apology

system fix

5000

780000

Chapter 5

10

Bank C

3

Free text field(abc, xyz)

Human

Internal Audit

Not necessary

Coaching

5

779981

Chapter 6

5

Chapter 9

1

Chapter 3

1

Bank C

4

Free text field(abc, xyz)

third party

Complaints

Not necessary

Training

8

15151

Chapter 7

1

Bank B

5

Free text field(abc, xyz)

System

Customer

Refund

system fix

99

11451151

Chapter 9

2

Chapter 2

1

1. Each incident can have one or more than one breach of the code. Breaches are identified in multiple columns of the same incidents.
2. Due to the table design, we have an issue with identifying the sum of breaches of a particular chapter or part. Code Chapter is a dimension table as below example-
   

   part_id	chapter_id
   Part 1	01
   Part 1	02
   Part 2	03
   Part 2	04
   Part 2	05
   Part 2	06
   Part 2	07
   Part 3	08
   Part 3	09
   Part 3	10
   Part 3	11
   Part 3	12
   Part 4	13
   Part 4	14

   Problem to Solve: I want to combine the multiple columns of code chapter and no of breaches so that, code chapter in one column and no of breaches in another column identified by the Ref_Key as below, which will make it easier to aggregate the breaches and apply filter on the code chapter
   

   Ref_Key	Code chapter breached	No of breaches
   1	Chapter 2	1
   1	Chapter 10	1
   2	Chapter 5	10
   3	Chapter 6	5
   3	Chapter 9	1
   3	Chapter 3	1
   4	Chapter 7	1
   5	Chapter 9	2
   5	Chapter 2	1

   Thanks in advance 🙂

[wdx223_Daniel · Accepted solution]

NewStep= #table({"Ref_ID","Code chapter breached","No of breaches"},List.TransformMany(Table.ToRows(PreviousStepName),each List.Select(List.Split(List.Skip(_,9),2),each Text.Trim(_{0}??"")<>""),(x,y)=>{x{1}}&y))

[rksharma86]

Thanks, I appreacite it very much! It worked worked like a charm. Honestly speaking I am having a little difficulty to understand the step (way outside of my current knowledge of M)