Power Query to Get Category Names from Above Row Value

[legalhustler]

Hello,

I need to transform this step of the query to get the category names (in bold) next to each of its corresponding items. The Table link in the second column for the category is not used in the expected results, it's for the corresponding items. I just need the category names next to each items.

Transform this:

Fruits	Table
Apples	Table
Oranges	Table
Drinks	Table
Coke	Table
Pepsi	Table
Sprite	Table
7 Up	Table
Dr. Pep	Table
Vegetables	Table
Onions	Table
Lettuce	Table
Mushrooms	Table

Expected Result:

Fruits	Apples	Table
Fruits	Oranges	Table
Drinks	Coke	Table
Drinks	Pepsi	Table
Drinks	Sprite	Table
Drinks	7 Up	Table
Drinks	Dr. Pep	Table
Vegetables	Onions	Table
Vegetables	Lettuce	Table
Vegetables	Mushrooms	Table

 

[legalhustler]

I didn't get a chance to edit the table but note the first column all have the word "category" in the first column (i.e. Category Fruits, Category Drinks, Category Vegetables), so whenever the word "category" is found it puts the full category name next to each item (Category Fruits, Category Drinks, Category Vegetables). That should be the trigger to determine how many rows of items each category has.

 

[alansidman]

Add a column, use Text.StartsWith to find all with "Category" and those that don't are null
Fill Down new column
Split the new column to separate Category
Remove Category Column
Move Other Column to first position
Filter out all rows containing Category from original data