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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Employee
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.