Convert a cell into a column entry in power query

[Oliehoek]

Hi,

I am uploading data from daily spreadsheets and the sheet has several tables in them. The first cell of the table is giving me the area the table is referring to and then the data, below this table the same is repeated several times. I would like to take that fist cell (for each table) and put it repeatedly in front or behind the table. How do I do this in Query to clean up my dataset?

Example

Area 1
	Result 1	Result 2	Result 3
Input 1
Input 2
input 3
Area 2
	Result 1	Result 2	Result 3
Input 1
Input 2
input 3
Area 3
	Result 1	Result 2	Result 3
Input 1
Input 2
input 3

To be transferred to

		Result 1	Result 2	Result 3
Area 1	Input 1
Area 1	Input 2
Area 1	input 3
		Result 1	Result 2	Result 3
Area 2	Input 1
Area 2	Input 2
Area 2	input 3
		Result 1	Result 2	Result 3
Area 3	Input 1
Area 3	Input 2
Area 3	input 3

In excel I would to IF(LEFT(B2)="Area", B2, A1), to copy the text above if the text does not contain Area, but I have no clue how to do this in Query.

Additionally I would consequently clear all rows without data. Would this then make the formula invalid?

[serpiva64 · Accepted solution]

Hi,

You can obtain this result with the steps of the attached file

Then it is not so clear for me what you need next.

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

[serpiva64 · Accepted solution]

Hi,

You can obtain this result with the steps of the attached file

Then it is not so clear for me what you need next.

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

[Oliehoek · Accepted solution]

Thanks!

It is the "Fill > Down" option that I was not aware of.