cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Can Power Query pick up new tabs from an Excel Workbook

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

2 REPLIES 2
Highlighted
Super User I
Super User I

Re: Can Power Query pick up new tabs from an Excel Workbook

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

Highlighted
Community Support
Community Support

Re: Can Power Query pick up new tabs from an Excel Workbook

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

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021