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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LMCarlson_ABB
Frequent Visitor

Changing from Folder.Files to Folder.Contents causes error

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"

6 REPLIES 6
VN999
Resolver I
Resolver I

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:

  1. 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.
  2. 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.
  3. 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.

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.

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.

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

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

lbendlin
Super User
Super User

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

lbendlin_0-1719786106894.png

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors