Forum Discussion

LoremIpsum's avatar
LoremIpsum
Regular Visitor
8 years ago
Solved

Importing dynamic named worksheets

I've developed many solutions in Excel with VBA years ago, and recently I've come back to it, but I see I have a lot to catch up on. I hope this is the right venue for my question. Please recommend a...
Tutorial Requests
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    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

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
8 years ago

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

monojchakrab's avatar
monojchakrab
Resolver III
4 years ago

Hey v-qiuyu-msft ,

 

This is an elegant solution and can be use for a small table wth a few columns as in this example.

But how can I make this work, if my table has over 100 columns? Can the a dynamic of list of columns be supplied where you have supplied the list of columns?

Any help appreciated.