Rows to column transformation with multiple files in a single table

[Anonymous]

Hi All,

I have imported FEb and Mar xlsx files from folder option and the table data sample is mentioned below, also every month file will be uploaded. 

1	Date	1-Feb	2-Feb	3-Feb	4-Feb
2	Apple	12	11	13	10
3	Orange	12	11	13	10
4	Grapes	12	11	13	10
5	Date	1-Mar	2-Mar	3-Mar	4-Mar
6	Apple	12	11	13	10
7	Orange	12	11	13	10
8	Grapes	12	11	13	10

I want to move from row 5 to 1 as mentioned below  also this has to work when new file will be uploaded on every month. Even Column 5 and Date can be skipped. Any Suggestion?

1	Date	1-Feb	2-Feb	3-Feb	4-Feb	5	Date	1-Mar	2-Mar	3-Mar	4-Mar
2	Apple	12	11	13	10	6	Apple	12	11	13	10
3	Orange	12	11	13	10	7	Orange	12	11	13	10
4	Grapes	12	11	13	10	8	Grapes	12	11	13	10

Regards

MM

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

You can use the table.split function to split rows by 4 in your sample. And then divide it to two queries. At last you can merge them together.

Please refer to the pbix file.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak]

@Anonymous , each month you should first  upload and then unpivot so that date come in row and then append .

Ideal data would be

Even, Fruit, Date, Value

1 , apple, 1-feb, 12

https://radacad.com/pivot-and-unpivot-with-power-bi

[Anonymous]

Hi Amit,

I have imported the file throught From Folder options. Hence, the files will be placed from diff. Location on monthly basis. The user will not do unpivot on every month...