MS Excel - [Expression.Error] The Csv.Document parameter 'Columes' is invalid

Question

Hi,

I'm having issues with running a Macros in excel, and getting the following error when clicking refresh all MS Excel - [Expression.Error] The Csv.Document parameter 'Columes' is invalid, this seems to be working on a PC where the code was created. not on any other one.

Not sure what is the issue ??

Answer 1

Thanks for your help, the power query version are different although both running MS office 2016

I cannot post the CSV as it contains confidential info.

Thanks for your help.

Thanks 

Mohannad

Answer 2

Hi

Although I noticed a couple of things that could be optimised I didn't see something - obvious - that could explain the [Expression.Error] The Csv.Document parameter 'Columns' is invalid error you're getting.

You mentioned this query works on the PC where it was built, not on others

As a general guidance queries should be build on a PC that has the lowest version of Excel/Power Query. So, if you have i.e. 5 PCs running Excel 2010, 2 running Excel 2013 and all other running Excel 2016/365 and expect the query to work everywhere you should built it on one of the PC that runs Excel 2010 and make sure all other PCs running Excel 2010 are up to date re. the version of Power Query installed

Back to the error you get... As there's nothing obvious, on a PC where it fails:

• Edit the query (launch the PQ editor as previously described)
• Click on each Step (on the right side) from top to bottom until you get the error. At least you will know which one is the source of the error

Not sure I'll be able to help further with this without a realistic sample of one of your CSV files. If nothing too confidential feel free to upload your workbook + a CSV to your OneDrive (or any other service), share the files and post the links to those files in your next reply - see How To here

Answer 3

Hi

Not sure why you talk about Macros (VBA) as error "[Expression.Error] The Csv.Document parameter 'Columes' is invalid" comes from a Get & Transform (aka Power Query) query. Without seing the query code it's vurtually impossible to help I'm afraid.

To get/see the query code:

• Click somewhere in the Table that is the result of the query (the one you want to Refresh)
• Above the Excel ribbon click on Query Tools
• Click Edit (on the left) to launch the Power Query editor
• On the Home tab click Advanced Editor. You should get a window like this (just an example) that contains the full query code

• Select all the text and Copy it
• Paste that text in your next reply

I suspect the query was built on a PC with a different language (Columes vs. Columns) and that "info" is probably hard-coded/static into the code, making the query not transportable as is. Not sure this will be easy to fix without having access to the complete environment (ths CSV file at least)

Answer 4

Hi,

Sorry for the late reply, the error was 'Columns' is invalid' not Columnes it is a mistake.

looking at the below I don't see anything that could be unique to the PC itself ? 

let

    Source = Folder.Files("G:\Daily reports\1. Report data\suite_data"),

    #"Sorted Rows" = Table.Sort(Source,{{"Date created", Order.Descending}}),

    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),

    #"Invoke Custom Function1" = Table.AddColumn(#"Kept First Rows", "Transform File from suite_data", each #"Transform File from suite_data"([Content])),

    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from suite_data"}),

    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from suite_data", Table.ColumnNames(#"Transform File from suite_data"(#"Sample File"))),

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type number}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"Column6", type text}, {"Column7", Int64.Type}, {"Column8", type any}, {"Column9", type text}, {"Column10", type any}, {"Column11", type number}, {"Column12", type any}, {"Column13", type text}, {"Column14", type any}, {"Column15", type text}, {"Column16", type text}, {"Column17", Int64.Type}, {"Column18", type text}, {"Column19", type any}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type any}}),

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column2", "Resv Status"}, {"Column1", "CONFIRMATION"}, {"Column6", "Guest Name"}, {"Column7", "Adults"}}),

    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Adults] <> 0)),

    #"Renamed Columns2" = Table.RenameColumns(#"Filtered Rows",{{"Column9", "Rate Code"}, {"Column11", "Rate Amount"}, {"Column13", "Offer"}, {"Column15", "Rm Type"}, {"Column16", "RTC"}}),

    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns2",{"Column17"}),

    #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns1",{{"Column18", "Arrive"}}),

    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns3",{"Column19"}),

    #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns2",{{"Column20", "Depart"}, {"Column21", "Sub Segment"}, {"Column22", "Company"}, {"Column23", "Agent"}, {"Column24", "Channel"}, {"Column25", "Created"}}),

    #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns4",{"Offer", "Resv Status", "Column26", "Agent"}),

    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Columns3", {{"Arrive", type date}}, "en-AU"),

    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"Depart", type date}}, "en-AU"),

    #"Changed Type with Locale2" = Table.TransformColumnTypes(#"Changed Type with Locale1", {{"Created", type date}}, "en-AU"),

    #"Filtered Rows1" = Table.SelectRows(#"Changed Type with Locale2", each ([Adults] <> null)),

    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Column3", "Column4", "Column5", "Column8", "Column10", "Column12", "Column14", "Company"})

in

    #"Removed Columns"