Convert a cell into a column entry in power query

Question

Hi,&nbsp;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?&nbsp;ExampleArea 1&nbsp;&nbsp;&nbsp;&nbsp;Result 1Result 2Result 3Input 1&nbsp;&nbsp;&nbsp;Input 2&nbsp;&nbsp;&nbsp;input 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Area 2&nbsp;&nbsp;&nbsp;&nbsp;Result 1Result 2Result 3Input 1&nbsp;&nbsp;&nbsp;Input 2&nbsp;&nbsp;&nbsp;input 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Area 3&nbsp;&nbsp;&nbsp;&nbsp;Result 1Result 2Result 3Input 1&nbsp;&nbsp;&nbsp;Input 2&nbsp;&nbsp;&nbsp;input 3&nbsp;&nbsp;&nbsp;&nbsp;To be transferred to&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Result 1Result 2Result 3Area 1Input 1&nbsp;&nbsp;&nbsp;Area 1Input 2&nbsp;&nbsp;&nbsp;Area 1input 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Result 1Result 2Result 3Area 2Input 1&nbsp;&nbsp;&nbsp;Area 2Input 2&nbsp;&nbsp;&nbsp;Area 2input 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Result 1Result 2Result 3Area 3Input 1&nbsp;&nbsp;&nbsp;Area 3Input 2&nbsp;&nbsp;&nbsp;Area 3input 3&nbsp;&nbsp;&nbsp;&nbsp;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.&nbsp;Additionally I would consequently clear all rows without data. Would this then make the formula invalid?

serpiva64 · Accepted Answer

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!
&nbsp;

Forum Discussion

Convert a cell into a column entry in power query

2 Replies

Related content

Open Mirroring CSV column types not converting

Convert Data Entered as Column Entries to Row Entries

Convert measure to column

Convert calculated column into measure

converting rows into column in powerquery

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