cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jdeem58 Helper I
Helper I

Retain folder or file information when expanding Excel data from Sharepoint

Hi,

I have been searching for an effective solution to retain folder or file information when navigating down to sheet level.

 

I have seen a number of similar posts, including this from Brian M however couldn't really understand it.

https://community.powerbi.com/t5/Desktop/Retain-file-name-column-when-using-Folder-as-a-data-source-...

 

I have found a workaround/ what I think is a clumsy solution however think there must be a more effective way of achieving.

 

My M code is as follows:

let
Source = SharePoint.Files("https://location.sharepoint.com/sites/sharepointname", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "filename.xlsm")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Sheet name", each Excel.Workbook([Content])),
#"Expanded Sheet name" = Table.ExpandTableColumn(#"Added Custom", "Sheet name", {"Name", "Data"}, {"Sheet name.Name", "Sheet name.Data"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Sheet name", each ([Sheet name.Name] = "Actuals Feed ADS" or [Sheet name.Name] = "Actuals Feed Comm" or [Sheet name.Name] = "Actuals Feed FS" or [Sheet name.Name] = "Actuals Feed Gov")),
#"Expanded Sheet name.Data" = Table.ExpandTableColumn(#"Filtered Rows1", "Sheet name.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}, {"Sheet name.Data.Column1", "Sheet name.Data.Column2", "Sheet name.Data.Column3", "Sheet name.Data.Column4", "Sheet name.Data.Column5", "Sheet name.Data.Column6", "Sheet name.Data.Column7", "Sheet name.Data.Column8", "Sheet name.Data.Column9", "Sheet name.Data.Column10", "Sheet name.Data.Column11", "Sheet name.Data.Column12"})
in
#"Expanded Sheet name.Data"

 

My reasoning follows:

1 - locate file of interest in sharepoint

2 - create a custom column to expand sheet information

3 - expand 2 headers 'name' and 'data'

4 - filter sheet name to those desired for further manipulation

5 - expand sheetname.Data to access row information for all sheets selected

 

 

I would be really interested to hear your thoughts on this and where this could be improved.

 

 

Thanks,

J  

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-kelly-msft
Microsoft

Re: Retain folder or file information when expanding Excel data from Sharepoint

Hi @Jdeem58

 

Sorry but you have done the best and there's no better choices.

 

 
Best Regards,
Kelly

View solution in original post

2 REPLIES 2
Microsoft v-kelly-msft
Microsoft

Re: Retain folder or file information when expanding Excel data from Sharepoint

Hi @Jdeem58

 

Sorry but you have done the best and there's no better choices.

 

 
Best Regards,
Kelly

View solution in original post

Highlighted
Jdeem58 Helper I
Helper I

Re: Retain folder or file information when expanding Excel data from Sharepoint

Well that's great then! 🙂

thanks for letting me know

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors