Normalize data after workbook tab merge.

[namdron]

I have a folder structure that contains workbooks; each with an 'export' tab.  I need to merge the 'export' tabs into a new worksheet.  I've figure out how to do this using the 'New Query' function in Power Query.  I have an issue in that each 'export' tab that is merged contains a row that is header type information.  It's in the form: 

So in reference to the picture, every 38 rows is a new 'export' tab.  From rows 4-38 are the weekly data.  Each 'export' tab section within the merged document has a different Site: <num> in the 1st, 39th, 39+38 rows ..... etc.

So my question.  How can I use the Power BI query editor to manipulate this data so that when I do a refresh the data gets transformed into the following: 

where the 'Site' column in the transformed data changed to match the correct Site ID number.

[v-lili6-msft]

hi, @namdron 

I think if you need to Append your data not Merge.

https://support.office.com/en-ie/article/append-queries-power-query-e42ca582-4f62-4a43-b37f-99e2b2a4...

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Best Regards,

Lin