Forum Discussion

Kashibaba's avatar
Kashibaba
Brass Contributor
Oct 15, 2019

Power Query Excel data from Variable / Dynamic Worksheet...

Hi All,  I'm using power query to pull data from a weekly file via  filepath and name in cell reference, which is working perfectly. However when I change week number, worksheet name for next week f...
admin
BI & Data Analysis
developer
office 365
power bi
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 15, 2019

Kashibaba 

You may change in Navigation step the code which is usually looks like

Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

on

Sheet1_Sheet = Source{0}[Data],

Other words, use relative reference instead of absolute one.

  • SamToledanes's avatar
    SamToledanes
    Copper Contributor
    Feb 18, 2021

    SergeiBaklan thanks for this man. this really helped me on appending all of the data as one table I'm new to queries, VBA and pivot so I'm in a newbie stage.

     

     

    • monojchakraborty's avatar
      monojchakraborty
      Copper Contributor
      May 10, 2022

      HiSamToledanes,

       

      Its an elegant solution as it becomes 'sheet-name' agnostic.

       

      I was wondering if it is possible to store the sheetname to import as a query parameter and then supply that where it is required to insert the name of the sheet?

       

      Appreciate

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        May 10, 2022

        monojchakraborty 

        You may name the cell in the grid which has sheet name, query it and use result in other queries, like

        getSheetName = Excel.CurrentWorkbook(){[Name="sheetname"]}[Content]

        where "sheetname" is the name of the cell.

  • Kashibaba's avatar
    Kashibaba
    Brass Contributor
    Oct 15, 2019
    Thank you Sergei.
    I removed 1st column (Name) showing sheet name added by query itself and refresh query. It did work and now sheet Name column refreshing as well without extra hard coding. Also I don't have Navigation step in my applied step may be because of setting file Path for the source.
    Thanks again for a quick suggestion which may be helpful in some other but similar cases.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 15, 2019

      Kashibaba 

      Yes, that's also the way if you have only one sheet. The idea is when connecting to Excel file connector takes sheets one by one in sequential order. First record is always for the first sheet. Instead of taking it by record metadata you may take just first record. In your case both

      let
    Source = Excel.Workbook(File.Contents("C:\Test\Book1.xlsx"), null, true),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data",...

      and

      let
    Source = Excel.Workbook(File.Contents("C:\Test\Book1.xlsx"), null, true){0}[Data]
in
    Source

      shall work.

Resources