I'm having an issue after I've imported data from excel. To explain:
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:
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?
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.
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.
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:
I'm so stumped on this, been pulling my hair out trying to figure it out