Need data extraction of a set of rows in a excel column

[Anonymous]

I have a sample column with data belonging to a key value. I want some key figures in a seperate column:

E.g.:

Merged

S1123

Origin: BLA

Des: ITL

Weight: 2222 kg

Piece: 34BG

340

S0298

Origin: QPD

Des: MGC

Weight: 3338 kg

Piece: N21 BG

221

Charge: 122MH

S2200

Origin: ANT

Des: LPD

Weight: 2788 kg

Piece: N331 BG

23x

....

...

 This is my sample dataset. 

I would like to extract Weight, Origin, Des and S**** (first value).

I have also given a pic of how the data looks when split into three columns.

What I require is Sum of (CHARGE IN AUD) for each job, and want Origin, Dest, weight from Merged Column. I am not sure how to retrieve it from here.

[TomMartens]

Hey @Anonymous ,

I'm wondering if the data you provides represents one value (represented in Power Query as a cell inside a single row?
If this is the case then I recommend splitting this into multiple columns isolating the content you are looking, then you can "clean" the content by removing blanks.

Hopefully, this provides an idea of how to tackle this challenge.

Regards,

Tom

[Anonymous]

I have got the below response. However, I need the Job no to repeat 3 times for Origin, Dest, and Weight figures in Custom.

[Anonymous · Accepted solution]

HI @Anonymous,

Did the ‘job No’ field already existed in your query table? If that's the case, you can replace these '1' rows to null and use ‘fill down’ feature to fill and repeat the ‘job No'.

Replace values and errors - Power Query | Microsoft Learn

Fill values in a column - Power Query | Microsoft Learn

If the above not help, please share some dummy data with table format then we can copy them to test.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng