Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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.
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,
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,
(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
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
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.
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,
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,
(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
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