Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-kelly-msft
Community Support
Community Support

Hi @Anonymous ,

 

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

 

 
Best Regards,
Kelly

View solution in original post

2 REPLIES 2
v-kelly-msft
Community Support
Community Support

Hi @Anonymous ,

 

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

 

 
Best Regards,
Kelly

Anonymous
Not applicable

Well that's great then! 🙂

thanks for letting me know

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.