Expression.Error Column of table not found

[WillBrowne]

Hello,

I'm using Power BI Desktop to pull in a bunch of .csv files from Sharepoint and then using a Sample File to help arrange the data.

When trying to refresh all data, i'm getting the following error:

OLE DB or ODBC error: [Expression.Error] The column 'TRX_TYPE_SORT' of the table wasn't found.

When in Edit Queries the tables load through all fine, which is where i get stuck. The report had been refreshing and working smoothly prior to Christmas.

The Advance Editor code i believe is causing the problem is below:

let
    Source = Csv.Document(Parameter2,[Delimiter=" ", Columns=55, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TRX_TYPE_SORT", type text}, {"TRX_TYPE", type text}, {"TRX_TYPE_DESCRIPTION", type text}, {"IS_TB_TRX_TYPE_01", type text}, {"CS_TB_AMOUNT_TYPE", type text}, {"TRX_CODE_SORT", type text}, {"TRX_CODE", type text}, {"DESCRIPTION", type text}, {"TB_AMOUNT", type text}, {"TRX_DATE", type text}, {"NET_AMOUNT", type text}, {"GUEST_LED_DEBIT", type text}, {"GUEST_LED_CREDIT", type text}, {"AR_LED_DEBIT", type text}, {"AR_LED_CREDIT", type text}, {"DEP_LED_DEBIT", type text}, {"DEP_LED_CREDIT", type text}, {"PACKAGE_LED_DEBIT", type text}, {"PACKAGE_LED_CREDIT", type text}, {"INH_DEBIT", type text}, {"INH_CREDIT", type text}, {"NON_REVENUE_AMT", type text}, {"TODAYS_ACCRUALS", type text}, {"DEPOSIT_AT_CHECKIN", type text}, {"PACKAGE_LED_TAX", type text}, {"DEP_TAX_LED_DEBIT", type text}, {"OWNER_LED_DEBIT", type text}, {"OWNER_LED_CREDIT", type text}, {"DEP_LED_DEBIT_PL_CZ", type text}, {"CS_CURRENCY_COUNT", type text}, {"C_TRX_CODE", type text}, {"C_RESORT", type text}, {"C_TRX_DATE", type text}, {"C_TB_AMOUNT_NET", type text}, {"C_DISP_CURRENCY", type text}, {"C_GUEST_LED_CREDIT", type text}, {"C_GL_DISP_CURRENCY", type text}, {"C_AR_LED_CREDIT", type text}, {"C_AR_DISP_CURRENCY", type text}, {"C_DEP_LED_CREDIT", type text}, {"C_DL_DISP_CURRENCY", type text}, {"C_INH_LED_CREDIT", type text}, {"C_IH_DISP_CURRENCY", type text}, {"C_OWNER_LED_CREDIT", type text}, {"C_OWNER_DISP_CURRENCY", type text}, {"", type text}, {"_1", type text}, {"_2", type text}, {"_3", type text}, {"_4", type text}, {"_5", type text}, {"_6", type text}, {"_7", type text}, {"_8", type text}, {"_9", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [TB_AMOUNT] <> null and [TB_AMOUNT] <> ""),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"TB_AMOUNT", type number}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"TB_AMOUNT"}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Errors",1),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Bottom Rows",{{"TRX_TYPE_SORT", Int64.Type}})
in
    #"Changed Type2"

Thanks in advance!

[v-juanli-msft]

Hi @WillBrowne 

It could be a step where you renamed or re-ordered columns, changed data types, removed other columns, replaced values.

You could try the methods suggested by members here.

As tested on my side, if i remove the "Columns=55," from the following code, it could refresh successfully.

Source = Csv.Document(Parameter2,[Delimiter=" ", Columns=55, Encoding=1252, QuoteStyle=QuoteStyle.None]),

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnkitBI]

Hi @WillBrowne  

Do your csv file has TRX_TYPE_SORT field present. Can you check in Step 2, "#"Promoted Headers" at Query setting , if this column is present. Double check by doing Refresh now in Query Editor.

Thanks

Ankit Jain