Forum Discussion

Learner1585's avatar
Learner1585
Helper I
3 years ago

Import selected multiple sheets from the latest excel file from sharepoint folder wout helper query

Hi,

I am posting first time here in forum.

I am looking for your kind expertise to assist me in my problem below:

 

Problem: Import selected multiple sheets from the latest excel file from sharepoint folder without helper query

 

Background:

  • Multiple excel files ( many sheets in each file) shall be saved in share point folder.
  • I need to pick the latest excel file.
  • From the selected latest file; import 3 selected sheets ( as 3 queries) separately.
    • Sheet names : ( DataSheet4, DataSheet5, DataSheet7)
    • With each query refresh , query should select the latest file and three sheets ( three queries)

       

       

       

       

       

       

      I know how to import one single file with single sheet only without helper query with the use of custom column.

      I want to be able to do same for my problem mentioned above to select the latest file and import selected multiple sheet separately as three queries( 3sheets) without helper query.

       

      = Table.AddColumn(#"Removed Other Columns", "Custom", each Table.PromoteHeaders(Table.Skip(Excel.Workbook([Content]){0}[Data])))

       


        

       


        

       

       

       

       

Need Help
Tips and Tricks
Tutorial Requests

8 Replies

  • mussaenda's avatar
    mussaenda
    Community Champion
    3 years ago

    Hi Learner1585 

     

    How do you define the latest file?

    You can do it without the helper query buuut why you don't want to use the helper query?

     

  • Learner1585's avatar
    Learner1585
    Helper I
    3 years ago

    Hi,

    Thank you for replying.

    Below code demonstrate it:

    Basically, I direct to share point folder, sort the file by file name ( file name is kept with date sequence)

    Then keep the first row ( i.e the top file) and this is my latest file

     

    let
    Source = SharePoint.Files(https://my.sharepoint.com/sites/salestest/, [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each [Folder Path] = https://my.sharepoint.com/sites/salestest/Shared Documents/Power Planning),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Name", Order.Descending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Content", "Name"}),
    #"Kept First Rows" = Table.FirstN(#"Removed Other Columns",1),
    Content = #"Kept First Rows"{0}[Content],

  • Learner1585's avatar
    Learner1585
    Helper I
    3 years ago

    I dont want to use helpe query coz it eats my query space and make it look dirty.

    Also I have felt it slows down the refresh query.

     

     

    • mussaenda's avatar
      mussaenda
      Community Champion
      3 years ago

      Hi Learner1585 ,

       

      I disagree with you, helper query doesn't slow down the refresh and you can always expand or collapse the group using the arrow.

       

      But if you still want your requirement, 

      After filtering the latest file, instead of clicking the  arrow button beside the content column, Click on the binary instead. 

       

      Then expand the data

       

      This will avoid the helper query that you dont want

       

      • Learner1585's avatar
        Learner1585
        Helper I
        3 years ago

        Hi,

         

        Thank you for your suggestion.

         

        But this does not seem to be working, I am sorry , I may have not understood exactly what you suggested.

        Imported all the files and sorted to see the latest files , deleted all other rows so that now only the latest file is kept.

         

         

         

         

        Clicked on binary which shows all the sheets in the file.

         

         

         

         

        Filtered only the desired sheet ( DataSheet4, DataSheet6 ,DataSheet7)

         

         

         

        Clicked on expand Table option which resulted in all the three sheets combined as one query one sheet which is not the desired outcome.