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

[Learner1585]

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])))

[Learner1585]

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]

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]

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.

[mussaenda]

Hi @Learner1585 ,

Before expanding duplicate this twice and filter only by the sheet you need

then expand.

That would work, right?

[Learner1585]

Hi,

Thank you.

This seems to be a nice work around.

Is there any better cleaner way to do this like we do by adding a custom column when its a single sheet file.

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

[Learner1585]

Ideally This is what mentioned below I want to achieve with the latest file in shared folder.

This is when I use the option of web import and map the power query to pick only one file.

All the sheet names are visible and I can multi select my desired files.

This results in multiple queries created for each of the imported sheet.

I want same thing to be happening but with the latest files and without helper query

[Learner1585]

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],

[mussaenda]

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?