Multiple sheets with the same format, problem with append data

[fatanalyst]

Hi guys,

I'm having an issue after I've imported data from excel. To explain:

• I've successfully imported an excel workbook with about 150+ worksheets in each
• Each sheet is a store housing Weekly sales split into columns by year, with the rows representing weeks 1-52
• Each worksheet represents a store and they largely have the same format

If you notice columns 2016 and 2017, there is data before Week 1 rows - this is the sales and trans data of the last week of the previous year:

• Data in 2017 column before the "Week 1" row is data for Week 52 of 2016
• Data in 2016 column before the "Week 1" rose is data for Week 52 of 2015

I have used the = Excel.Workbook(File.Contents("name of workbook.xlsm"), null, true) formula in query to bring all of the worksheets in but I can't figure out how to get the data before week 1 to where it should be - if that makes sense. 

I have formatted the data in Power BI to keep each column, ie 2015 Sales, 2015 Trans, 2016 Sales, 2015 Trans - and so on. 

Can anyone help me? 

[BobBI]

Hi ,

It looks like values for Week1 is not appearing against those value for 2016 and 2017 in your excel . did you try 'FILL UP' in the query editor-transform tab ,

You may want to create a seperate quiery for 2016 and 2017 to align week1 values and then append query to main query.

Hope this helps.

Bob

[fatanalyst]

Thank you guys for the replies. Let me try and then get back to the forum. 

[fatanalyst]

I can't seem to get that work as when i import the data via query editor, i import all stores so over 150 worksheets. The columns are laid out as shown in the spreadsheet. I've tried creating 3 different queries:

• Query 1 = Sales 2016
• Query 2 = Sales 2017
• Query 3 = Sales 2018

I'm so stumped on this, been pulling my hair out trying to figure it out 

[v-jiascu-msft]

Hi @fatanalyst,

Do you mean moving the data to where it should be? Can you share a sample? Or show us how the data looks in the Query editor. 

Best Regards,

Dale

[fatanalyst]

yeah exactly. 

here's the sample https://docs.google.com/spreadsheets/d/1fYcnFpybCwwSJdYzhye_oc93OLa_bdnNbJJHicBMElw/edit?usp=sharing