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

Stachu's avatar
Stachu
Community Champion
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