Forum Discussion
The Csv.Document parameter 'Columns' is invalid.
My IT department managed to fix the issue. We went through the advanced editor in query mode and manually changed the coding to get it to work.
This is how we did it:
This line of text is incorrect:
Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=","columns=10, Encoding=1252]),
The columns=10 was counting the columns in the document (10 is correct) but something was going wrong. We deleted this text but lef tin the commas and republished the file. It then failed again due to this error:
The Csv.Document parameter 'QuoteStyle' is invalid. From memory the advanced editor text looked like this:
Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=","columns=10, Encoding=1252]), QuoteStyle=QuoteStyle""
We deleted the QuoteStyle text and republished. The dashboard now refreshes using the automated refresh feature in Pro.
Thanks,
Giles
let
Source = Folder.Files("C:\Users\CaitlinKnox\SharePoint\LifeCycle Reports - SPU"),
#"Filtered Rows2" = Table.SelectRows(Source, each ([Extension] = ".csv")),
#"Combined Binaries" = Binary.Combine(#"Filtered Rows2"[Content]),
#"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter="#(tab)", Columns=10, Encoding=1200, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV"),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"User (Login Name)", type text}, {"User", type text}, {"URL", type text}, {"Type", type text}, {"Action", type text}, {"Time", type datetime}, {"Details", type text}, {"", type text}, {"_1", type text}, {"_2", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [User] <> null and [User] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Details", "", "_1", "_2", "User (Login Name)"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([User] = "Broker Associate Level - The NT Group - Joe Nemastil" or [User] = "Broker Executive Level - Nology - Brendon Liner" or [User] = "Broker Master Level - Integrated - Mary Heinen" or [User] = "Broker Professional Level - Millermiller Industrial Solutions - Karl Miller" or [User] = "Broker Professional Level - Muhammad Alam" or [User] = "Broker Professional Level - TechNoir - James Velco" or [User] = "Broker Professional Level - USPCNET - Elias Kousoulas" or [User] = "Greg LaCoste" or [User] = "Heather Raymond" or [User] = "Joellyn Mayer" or [User] = "John Kennedy" or [User] = "Morgan Spencer" or [User] = "Scott Morgan")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows1","URL",Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),{"URL.1", "URL.2", "URL.3", "URL.4", "URL.5", "URL.6", "URL.7", "URL.8", "URL.9", "URL.10", "URL.11"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"URL.1", type text}, {"URL.2", type text}, {"URL.3", type text}, {"URL.4", type text}, {"URL.5", type text}, {"URL.6", type text}, {"URL.7", type text}, {"URL.8", type text}, {"URL.9", type text}, {"URL.10", Int64.Type}, {"URL.11", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"URL.1", "URL.2", "URL.3", "URL.5", "URL.9", "URL.10", "URL.11"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns1","URL.8",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"URL.8.1", "URL.8.2", "URL.8.3", "URL.8.4"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"URL.8.1", type text}, {"URL.8.2", type text}, {"URL.8.3", type text}, {"URL.8.4", type text}}),
#"Merged Columns" = Table.CombineColumns(#"Changed Type2",{"URL.8.1", "URL.8.2", "URL.8.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"URL"),
#"Removed Columns2" = Table.RemoveColumns(#"Merged Columns",{"URL.8.4"})
in
#"Removed Columns2"
Caitlin_KnoxTry this and see if it works, i have removed some information and changed the coding from 1200 to 1252. 1252 is the encoding of characters to English.
let
Source = Folder.Files("C:\Users\CaitlinKnox\SharePoint\LifeCycle Reports - SPU"),
#"Filtered Rows2" = Table.SelectRows(Source, each ([Extension] = ".csv")),
#"Combined Binaries" = Binary.Combine(#"Filtered Rows2"[Content]),
#"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=", ", Encoding=1252]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV"),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"User (Login Name)", type text}, {"User", type text}, {"URL", type text}, {"Type", type text}, {"Action", type text}, {"Time", type datetime}, {"Details", type text}, {"", type text}, {"_1", type text}, {"_2", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [User] <> null and [User] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Details", "", "_1", "_2", "User (Login Name)"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([User] = "Broker Associate Level - The NT Group - Joe Nemastil" or [User] = "Broker Executive Level - Nology - Brendon Liner" or [User] = "Broker Master Level - Integrated - Mary Heinen" or [User] = "Broker Professional Level - Millermiller Industrial Solutions - Karl Miller" or [User] = "Broker Professional Level - Muhammad Alam" or [User] = "Broker Professional Level - TechNoir - James Velco" or [User] = "Broker Professional Level - USPCNET - Elias Kousoulas" or [User] = "Greg LaCoste" or [User] = "Heather Raymond" or [User] = "Joellyn Mayer" or [User] = "John Kennedy" or [User] = "Morgan Spencer" or [User] = "Scott Morgan")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows1","URL",Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),{"URL.1", "URL.2", "URL.3", "URL.4", "URL.5", "URL.6", "URL.7", "URL.8", "URL.9", "URL.10", "URL.11"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"URL.1", type text}, {"URL.2", type text}, {"URL.3", type text}, {"URL.4", type text}, {"URL.5", type text}, {"URL.6", type text}, {"URL.7", type text}, {"URL.8", type text}, {"URL.9", type text}, {"URL.10", Int64.Type}, {"URL.11", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"URL.1", "URL.2", "URL.3", "URL.5", "URL.9", "URL.10", "URL.11"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns1","URL.8",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"URL.8.1", "URL.8.2", "URL.8.3", "URL.8.4"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"URL.8.1", type text}, {"URL.8.2", type text}, {"URL.8.3", type text}, {"URL.8.4", type text}}),
#"Merged Columns" = Table.CombineColumns(#"Changed Type2",{"URL.8.1", "URL.8.2", "URL.8.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"URL"),
#"Removed Columns2" = Table.RemoveColumns(#"Merged Columns",{"URL.8.4"})
in
#"Removed Columns2"
I get the error 'Token Comma expected' When I click show error, it highlights the text as shown in the screen capture. But looking at the lines around, I cant determine where it is expecting a comma.
This happened to me also. By changing the inormation in the first couple steps this can then have an effect on the remaining steps. Is it possible for you in the query editor to remove all steps after the promoted headers step? Then see what the data looks like and go through and do your editing again?
This happened to me also. By changing the inormation in the first couple steps this can then have an effect on the remaining steps. Is it possible for you in the query editor to remove all steps after the promoted headers step? Then see what the data looks like and go through and do your editing again?