Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
snaise
Frequent Visitor

Join all sheets in one excel after transforming them individually

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

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

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

 

 

 

View solution in original post

3 REPLIES 3
AlienSx
Super User
Super User

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
Frequent Visitor

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
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors