Can the actions of 2 subs be replaced/carried out in Power Query?|Power Query|Excel Forum|My Online Training Hub

Excel 2019.
Windows 10.

Hi everyone.

I do not know if this is possible and if I do not ask then I will not know.

I will also be completely open and say I know nothing about nor understand Power Query beyond what you can do in the transform window.

The Query code was created by recording the query as a macro.

I have Power Query that imports a text delimited text file with essentially what is Parent and Child data.
One value in Column B (the parent) is related to the values in a number of rows below until the next parent value.

Example 1

During the import process the query removes unwanted rows then pivots the remaining data from vertically based to horizontal.

This works perfectly fine except it leaves two extra steps which, at the moment, are completed by two other Subs once the Query results are written to a worksheet.

The first sub moves all of the child values, which are spead out in various columns and on various rows, up to the parent row. This is a very quick process even on large data sets of 10,000 rows or more.

The second is problematic in that it does not like large datasets and often renders Excel as "not responding".

After the first sub has moved the child values up to the parent, it leaves a number of blank rows which over a large worksheet can run into thousands.

Starting from the last row, this sub checks the rows are empty then deletes the entire row and follows this logic up to Row 2.

I wonder if there is a better way as the internet is full of posts saying Power Query is the secret to handing large sets of data.

My question is this...
Can the actions of two subs be performed by the query and if so how?

I tried using the Transform menu "fill up" but that does not respect the Parent-Child nature the data as the image below shows.

Example 2

As for the second query, if I can get the first one completed by the query then I can just remove the blank rows in the query.

I know Querys use M Language but I know nothing of it and, if I am honest, I only just about have a small understanding of basic VBA.

I am unable to find, using search engines, any information on how to write or how to convert or write the equalivent of what the subs are doing in M Language.

Is this even possible?

I attach a small example workbook with a example delimited text file.

Many thanks.