Forum Discussion

lekkerbek's avatar
lekkerbek
Helper IV
9 years ago
Solved

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 supp...
Need Help
  • Stachu's avatar
    Stachu
    9 years ago

     

    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's avatar
Greg_Deckler
Community Champion
9 years ago

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.