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
craig811
Helper III
Helper III

Can Power Query pick up new tabs from an Excel Workbook

Hi ,

 

When new tabs are added to an Excel Workbook could I get Power Query pick them up and assign the same steps to that tab as all of prevouis tabs or will I need to do this manually?

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @craig811 

First connecting to excel normally, then in the Navigator pane right-click on the excel name and select "Transform/edit" rather than selecting any of the individual worksheets.

Add an index column in this original table.

Capture6.JPG

Then duplicate this table "3 2 xlsx" and get a "tempelate" table,add steps below in this query,

Filtered Rows->Removed Other Columns except "Data"->Expanded Data column->Promoted Headers,

Capture7.JPG

let
    Source = Excel.Workbook(File.Contents("C\****3.2.xlsx"), null, true),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] = 1),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3"}, {"Data.Column1", "Data.Column2", "Data.Column3"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Then right-click on "tempelate" query, select "create function", then get a function "get data", you could chnage the function as below,

Capture8.JPG

(index as number) => let
        Source = Excel.Workbook(File.Contents("C:\****\3.2.xlsx"), null, true),
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
        #"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] = index),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
        #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3"}, {"Data.Column1", "Data.Column2", "Data.Column3"}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true])
    in
        #"Promoted Headers"

Enter 1, 2, 3 and invoke, you could get sheet1, sheet2, sheet3.

 

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

There is much easier and more elegant way how to do it:

1) Import the excel (doesn't! matter if static file or google sheet),
    select from all its tabs only 1 (doesn't matter which one),
2) in transformations delete all steps except the very first one,
3) in the table in first step, delete all columns except of "Data" one,
4) click on the "expand" button on the column Data,
5) every column is by default selected, so click OK,
6) now you have all the tabs connected via UNION in 1 table, and you can filter all unnecessary rows and use your table as you wish

v-juanli-msft
Community Support
Community Support

Hi @craig811 

First connecting to excel normally, then in the Navigator pane right-click on the excel name and select "Transform/edit" rather than selecting any of the individual worksheets.

Add an index column in this original table.

Capture6.JPG

Then duplicate this table "3 2 xlsx" and get a "tempelate" table,add steps below in this query,

Filtered Rows->Removed Other Columns except "Data"->Expanded Data column->Promoted Headers,

Capture7.JPG

let
    Source = Excel.Workbook(File.Contents("C\****3.2.xlsx"), null, true),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] = 1),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3"}, {"Data.Column1", "Data.Column2", "Data.Column3"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Then right-click on "tempelate" query, select "create function", then get a function "get data", you could chnage the function as below,

Capture8.JPG

(index as number) => let
        Source = Excel.Workbook(File.Contents("C:\****\3.2.xlsx"), null, true),
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
        #"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] = index),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
        #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3"}, {"Data.Column1", "Data.Column2", "Data.Column3"}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true])
    in
        #"Promoted Headers"

Enter 1, 2, 3 and invoke, you could get sheet1, sheet2, sheet3.

 

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Jimmy801
Community Champion
Community Champion

Hello @craig811 

 

you can create an automatism.

First you have to create a function (you can transform a query into a function automatically) that pics ap a sheet (not the file itself) of an Excel file. 

Then you use the file Excel-Workbooks with File.Contents. This function returns a table with all sheets. In case filter for the sheets you need. As last step  apply your created function and invoke it in a added column.

 

Give it some try. If you have detailed question when you stuck, just ask

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

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