cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Multiple sheets with the same format, problem with append data

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? 

 

CT Store.PNG

 

 

 

 

 

 

 

 

 

 

 

 

5 REPLIES 5
Highlighted
Microsoft
Microsoft

Re: Multiple sheets with the same format, problem with append data

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Multiple sheets with the same format, problem with append data

Highlighted
Resolver III
Resolver III

Re: Multiple sheets with the same format, problem with append data

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

Highlighted
Frequent Visitor

Re: Multiple sheets with the same format, problem with append data

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

Highlighted
Frequent Visitor

Re: Multiple sheets with the same format, problem with append data

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 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors