Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
fatanalyst
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
BobBI
Resolver III
Resolver III

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

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

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
Microsoft Employee
Microsoft Employee

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.