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
