topic Re: Changing from Folder.Files to Folder.Contents causes error in Power Query

Changing from Folder.Files to Folder.Contents causes error

LMCarlson_ABB — Fri, 28 Jun 2024 16:24:46 GMT

In the following query, if Folder.Files is changed to Folder.Contents, the following error is reported. What is the cause and what is the remedy?

An error occurred in the ‘Transform File (3)’ query. Expression.Error: The parameter is expected to be of type Text.Type or Binary.Type.
Details:
[Table]

let
Source = Folder.Files("C:\WDS2\Projects\FTP\Production\UHC Work"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (3)", each #"Transform File (3)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (3)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Portal/EDI ID", type text}, {"Customer #", type text}, {"Ship to #", type text}, {"Office #", type text}, {"Invoice Date", type date}, {"Invoice #", type text}, {"Invoice Type", type text}, {"Order Class", type text}, {"Customer PO#", type text}, {"CR Memo #", type text}, {"Billing Model", type text}, {"Patient Name", type text}, {"Patient Mdse", type number}, {"Tax City", type number}, {"Tax Rate Used", type number}, {"Tax County", type number}, {"Tax Other", type number}, {"Tax State", type number}, {"Tax Total", type number}, {"Patient Freight", type number}, {"Addons - Mdse", type any}, {"$Addons - Mdse", type number}, {"Addons - Other", type text}, {"$Addons - Other", type number}, {"Coupon Code", type any}, {"Coupon Discount", type number}, {"Patient Total", type number}, {"Insurance Benefit", type number}, {"CC Type", type text}, {"Patient Payment", type number}, {"Discrepancy", type number}, {"Ship Via", type text}, {"Freight Addon(s)", type text}, {"$Freight Addon(s)", type number}, {"Freight Credits(s)", type text}, {"$Freight Credit(s)", type number}, {"Contract Mdse", type number}, {"Contract Mdse Add", type any}, {"$Contract Mdse Add", type number}, {"Contract Freight", type number}, {"Contract Other", type text}, {"$Contract Other", type number}, {"Prescription Verification", type number}, {"CC Fee", type number}, {"Admin Fee", type number}, {"Contract Tax", type number}, {"Contract Total Due", type number}, {"AvaTax Proc. Fee", type number}, {"Partner Share", type number}, {"Wholesale Tax", type number}, {"Wholesale Mdse", type number}, {"Misc. Income", type number}, {"Due From/(To) Partner", type number}, {"JE SOURCE", type text}, {"1116 Debit", type number}, {"1116 Credit", type number}, {"1400 Debit", type number}, {"1400 Credit", type number}, {"3000 Debit", type number}, {"3000 Credit", type number}, {"4000 Debit", type number}, {"4000 Credit", type number}, {"4120 Debit", type number}, {"4120 Credit", type number}, {"4700 Debit", type number}, {"4700 Credit", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Admin Fee", "AvaTax Proc. Fee", "Tax City", "Tax Rate Used", "Tax County", "Tax Other", "Tax State"})
in
#"Removed Columns"

Re: Changing from Folder.Files to Folder.Contents causes error

lbendlin — Sun, 30 Jun 2024 22:22:24 GMT

The only difference is that Folder.Contents also lists the subfolders (indicated by "Table")

If you filter these out then you should get your expected result.

Re: Changing from Folder.Files to Folder.Contents causes error

VN999 — Sun, 30 Jun 2024 22:31:16 GMT

The error message "Expression.Error: The parameter is expected to be of type Text.Type or Binary.Type" typically occurs in Power BI when there is a mismatch in data types during transformations or when using a function that expects a specific type of input.

Here are a few steps to troubleshoot and fix this issue:

Step-by-Step Troubleshooting:

Identify the Source of the Error:
- Go to the Power Query Editor by clicking on "Transform data" in the Home tab.
- Look for the query named 'Transform File (3)'.
- Check the applied steps in the query settings pane to identify where the error occurs.
Check Data Types:
- Ensure that the columns used in the transformation are of the correct data type (Text.Type or Binary.Type).
- If necessary, change the data type by selecting the column and then using the "Data Type" dropdown in the Transform tab.
Review Applied Steps:
- Go through each step in the query to find where the type mismatch occurs.
- If you find a step that applies a function expecting text or binary data but receives a different type, correct the data type before this step.

Re: Changing from Folder.Files to Folder.Contents causes error

LMCarlson_ABB — Mon, 08 Jul 2024 18:35:11 GMT

I'm pretty sure the error is with this "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)".

Unfortunately, I'm very new to PQ and I don't have a clue how to fix it.

Re: Changing from Folder.Files to Folder.Contents causes error

lbendlin — Mon, 08 Jul 2024 19:17:35 GMT

You would study what Transform File (3) does, and then completely replace with your own code. Keep learning Power Query, it's a useful skill to have.

Re: Changing from Folder.Files to Folder.Contents causes error

LMCarlson_ABB — Wed, 10 Jul 2024 22:10:16 GMT

I would study Transform File (3) if I could find its source and/or documentation. So far that has elueded me.

Re: Changing from Folder.Files to Folder.Contents causes error

lbendlin — Wed, 10 Jul 2024 23:02:29 GMT

Go to Power Query, and you find it on the left side in its own group.