How to get SharePoint.Contents import in PowerQuery to preserve the file name of an XSLX as a field?

[gemcityzach]

Hey folks,

I have a bunch of folders stored in SharePoint filled with various data, mostly in CSV. Each folder is a monthly snapshot of a different table from a database that we do not have a direct connection to.

Presently, we're importing the data into PowerBI using the SharePoint Folder import and CSV.Document import method. Each file is named with the date of the snap shot, like 02-01-2024.csv or 03-01-2024.csv. Some of the files are XSLX and while they have the same file names like '02-01-2024' they will have a tab named "export_name" or something like that.

During the import for CSV files, we get a tidy little column called Source.Name which is then converted to a date field for incremental refresh purposes. However, for the XLSX files, we're not getting that field on import. It's like PowerQuery is just dropping it.

Is there a way to get the file name added as a field during the import step?

[amitchandak · Accepted solution]

@gemcityzach , Usually Power Query does not drop. But Source.Name can be name also generated because of some other field. Check if it got renamed and later dropped as not required columns in a step (remove columns)

[gemcityzach]

Looks like PowerQuery did automatically drop that column but I was able to modify it in the Steps to fix it and add it back in