Forum Discussion
Importing dynamic named worksheets
Hi LoremIpsum,
The query FirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data], will filter the Source table firstly based on the condition [Kind] column has "Sheet" value, then retrieve data from the first row of [Data] column. You can see below sample:
Source tableValues from Source table first row of [Data] column when [Kind] has "Sheet" value
In your scenario, assume there is only one sheet (contains two columns) in the Excel file, and this sheet name is changed dynamically. You can write the Power Query like below:
let
Source = Excel.Workbook(File.Contents("C:\Users\<user name>\Desktop\New Microsoft Excel Worksheet.xlsx"), null, true),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2"}, {"Data.Column1", "Data.Column2"})
in
#"Expanded Data"As above query doesn't specify sheet name, though the Excel sheet name is changed, the above query can always get data.
Best Regards,
QiuyunYu
Hi LoremIpsum,
let me try a different explanation why your formula works like you want it:
1) Filters the table for those rows, who have "Sheet" in column "Kind". So if you have other object types like tables or named ranges, you assure this way that they won't interfere
2) {0} selects one row from your table and returns it as a record. As M starts to count at zero, this actually fetches the first row.
3) [Data] selects the column "Data" and as you're on the record-level already, you will directly receive the table from that cell.
ImkeF Thank you for the education. The code wasn't executing like I thought it was, and your explanation helped clear that up.