Forum Discussion
Multiple sheets with the same format
I have a client who has a franchise organisation. They report the purchases of 40 shops with each supplier. There are around 30 suppliers. So basically I have an excelsheet where each sheet is a supplier and on that sheet there is a list of the 40 shops with the total value of purchases made in a quarter.
The format of the sheet is always the same.
I would like to upload the sheet into Power BI and show some reports. My first idea would be:
- upload 30 sheets
- remove rows and columns that I don't need, change format, etc
- unpivot columns
Problem: I don't want to do this 30 times (for each supplier). Is there a way to use the applied steps in a query in another table (sheet)?
After that I'm thinking about merging the tables into 1 big table by using the DAX Union function.
Is this the way do to it, or is there an easier way?
paste the code below in the query editor
replace file path with your file
then expand the tables using two arrows icon next to the Data column
let
Source = Excel.Workbook(File.Contents("C:\Users\stach_000\Desktop\data.xlsx"), null, true),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Data"})
in
#"Removed Other Columns"it would still require some cleaning, but it's quite fast
alternatively if sheets are not in a single file you can create function where filename would be parameter, it appends data automaticaly then
5 Replies
paste the code below in the query editor
replace file path with your file
then expand the tables using two arrows icon next to the Data column
let
Source = Excel.Workbook(File.Contents("C:\Users\stach_000\Desktop\data.xlsx"), null, true),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Data"})
in
#"Removed Other Columns"it would still require some cleaning, but it's quite fast
alternatively if sheets are not in a single file you can create function where filename would be parameter, it appends data automaticaly then
I believe that you want an Append Query versus a Merge Query. So, 30 queries that you then append together. You can actually do this with a single query if you're OK writing the M code. I have an article on this (it deals with Merge queries but the same technique should work with Append queries: http://social.technet.microsoft.com/wiki/contents/articles/32915.power-bi-merge-query-with-m.aspx
Something else that you could consider, break each sheet out into its own Excel file and put them all in the same folder. You could then use Folder as a data source and it will append them all together automagically.
You need 1 function where you perform all your transformation-steps that you then apply in an add-column step. You just have to expand that column then. No need for a merge or append. It mimics the From-Folder-method, just that you don't have to split up your existing xls-sheet into separate files:
Excel.Workbook(File.Contents(..YourFilePath..), null, true)
This will return the content of your Excel-file in a format where the content of every sheet sits in one row:
You then add a column where you pass the [Data]-column to your function - that should be it.
... oops - overlap with post from Stachu who suggested the same method
Anyway - put your function into a new column :-)
Thank you all for the great suggestions.
I've tried Stachu/Imke's suggestion and (of course) needs some work, but appears to be what is the right way in my specific case.
