Expression Error

[Tun]

I am getting this error while using the code generated by power query editor and when I paste the same in excel it gives the following error. 

The Code

let
Source = SharePoint.Files("xxxxxx/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Apr 2024.csv" or [Name] = "Aug 2024.csv" or [Name] = "Dec 24.csv" or [Name] = "Jan 2025.csv" or [Name] = "Jul 2024.csv" or [Name] = "June 2024.csv" or [Name] = "May 2024.csv" or [Name] = "Nov 2024.csv" or [Name] = "Oct 2024.csv" or [Name] = "Sep 2024.csv")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Workstream", type text}, {"Created Date", type date}, {"Created By: Full Name", type text}, {"Work Order Number", Int64.Type}, {"Work Order Line Item Number", Int64.Type}, {"Install ID", type text}, {"Installed Asset ID", type text}, {"New meter serial number", Int64.Type}, {"Rejected new meter serial number", type any}, {"New LCE serial number", Int64.Type}, {"Rejected new LCE serial number", type any}, {"Service Territory: Name", type text}, {"Postcode", type text}, {"Job Code", type text}, {"New meter installation date", type date}, {"Owner: Full Name", type text}, {"Completed Comments", type text}, {"MI Meter Size", Int64.Type}, {"MI In Line or Concentric", type text}, {"House/Flat Number", type text}, {"Address 1", type text}, {"Postcode2", type text}, {"What3Words", type text}, {"Proactive vs Reactive", type text}, {"Sub Workstream", type text}, {"Record Type", type text}})
in
#"Changed Type"

The Error

[V-yubandi-msft]

Hello @Tun ,

We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.

Your feedback is valuable to us, and we look forward to hearing from you soon.

Thank You.

[V-yubandi-msft]

Hello @Tun ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we provided for your issue worked for you  or let us know if you need any further assistance?

Your feedback is important to us, Looking forward to your response. 

Thank You.

[Omid_Motamedise]

It seems like the issue is that the reference to "Sample File" isn't recognized in the context of your Excel query. In Power BI, when you use the #"Transform File" function, it's typically referring to a sample structure used during transformation. If Excel can't find "Sample File", it's because this reference is dynamic and needs to be explicitly defined.

To fix it, identify the Transform Function: You need to replace #"Transform File"(#"Sample File") with an actual reference to the function or a properly defined sample.

[V-yubandi-msft · Accepted solution]

Hi @Tun ,

Thank you for reaching out to the Microsoft Fabric Community. We appreciate your response, @jgeddes & @Greg_Deckler . The issue likely arises due to missing supporting queries when copying the code from Power BI to Excel.

Please try the following steps to resolve this.

1️. Copy All Supporting Queries -  Ensure all supporting queries, including the "Sample File" query and any related queries, are copied when transferring the Power Query code from Power BI to Excel. These queries are automatically created during the file combination process in Power BI and are essential for the script to function.

2️. Recreate the Sample File Query in Excel -  If the "Sample File" query is missing or incorrectly referenced, manually recreate it in Excel’s Power Query editor to maintain the correct transformations.

3️. Manually Combine Files [Alternative Approach] -  If the issue persists, consider manually loading each file into Excel and then appending them together instead of using the automated file combination feature.

By ensuring all dependencies are present in Excel and adjusting the approach if needed, you should be able to resolve the error.

I hope this helps. Please let me know if you need further clarification.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[Greg_Deckler]

@Tun Looks like maybe a folder query or something. So, in Power BI there was likely a parameter or other query called "Sample File" but it doesn't exist in Excel.

[Tun]

I am fetching data from the sharepoint folder, it has multiple .csv file. When I transform it in power bi and do all the editing like deleting columns, adding columns blah blah. Now when i click on advance editor and copy the code and paste it in excel to fetch the data in excel, it gives this error. But on the contrary if i load a single file from the same folder, it works perfectly fine when i paste the code in excel.

I have checked on the left pane of power query, the Sample File and Transform file, name is same, it exist as well. But something is wrong.

[jgeddes]

#"Sample File" may be referring to the query that is created when using the UI to combine files. 
If you are copying code from PBI to Excel you need to copy all of the supporting queries as well.