Join all sheets in one excel after transforming them individually

[snaise]

have an excel file with multiple sheets which I want first to transform them individually (remove 3 top rows, promote headers, remove top row, unpivot columns that are not column1=), and then annex them all together.

Is that possible? I tried to use a transform from combining files, but I just get an error becasue I'm trying to transform tables instead of a binary.

Thanks in advance!

Sam

[AlienSx · Accepted solution]

hi, @snaise 

1. Create a custom function with all your transformations steps.

2. Read your file with Excel.Workbook(File.Contents("C:\Book1.xlsx"), null, true)

3. Filter [Kind] column to get sheets only. 

4. Apply your function to [Data] column: Table.TransformColumns(Source, each {"Data", my_function})

5. Table.Combine [Data] column

So 

let
    Source = Excel.Workbook(File.Contents("C:\Book1.xlsx"), null, true),
    filter_sheets = Table.SelectRows(Source, each ([Kind] = "Sheet")),
    my_function = (t as table) =>
        let 
            a = Table.RemoveFirstN(t, 3),
            b = Table.PromoteHeaders(a),
            c = Table.RemoveFirstN(b, 1),
            d = Table.UnpivotOtherColumns(c, {"Column1"}, "Attribute", "Value")
        in d,
    transform = Table.TransformColumns(filter_sheets, {"Data", my_function}),
    combine = Table.Combine(transform[Data])
in
    combine

[snaise]

Hi Philip, thanks for answering.

The question is that is an excel file were they add each weeks info in a new tab, so do it individually doesn't work for me. I'm looking for a way were you can deal with them as files you combine, with one generic transformation for all of them.

[PhilipTreacy]

Hi @snaise 

Yes this is possible.  You just need to load each sheet individually with its own query, do your transformations, then combine the queries.

To load a sheet you'd use the Excel Workbook connector then choose the sheet, the query looks like this

let
    Source = Excel.Workbook(File.Contents("D:\temp\Book1-3-pgt2.xlsx"), null, true),
    Source_Sheet = Source{[Item="Source",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Source_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

To combine the sheets you can either merge them or append them.  I'm not sure what you want but happy to help if you need it.  

This article also descibes how to combine Excel worksheets with power query.

Regards

Phil