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 file changes as well and producing an error. 

How I can change M.Code to take data from first sheet in workbook as there is only one sheet in every week file? 

Thanks for ideas.

admin
BI & Data Analysis
developer
office 365
power bi
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    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

    • 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
        MVP
        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