Multiple sheets with the same format

[lekkerbek]

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?

[ImkeF]

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 🙂

[lekkerbek]

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. 

[Stachu · Accepted solution]

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

[Greg_Deckler]

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.

[Vvelarde]

@lekkerbek

In your case i 'll prefer first Merge all the sheets and then make the Transformation Data.