You're welcome 🙂
Yes, that query was just an example of the process to determine the number of columns.
It can equally be applied to a file from SharePoint or elsewhere with some adjustment.
I would actually recommend creating a function to make your life easier, so that it can be applied to SharePoint files within other queries.
Here is one I created and tested myself just now.
Paste this code into a blank query and call it CsvDocumentVariableColumns (or another name of your choosing).
let
func = (
#"File Contents" as binary,
optional #"Sample Rows" as number,
optional Delimiter as text,
optional Encoding as number,
optional #"Quote Style" as number
) as table =>
let
// 1. SET PARAMETERS TO DEFAULT VALUES IF NOT SPECIFIED
// Set Delimiter to tab if not specified
DelimiterFinal = Delimiter ?? "#(tab)",
// Set Encoding to 1254 if not specified
EncodingFinal = Encoding ?? 1254,
// Set QuoteStyle to QuoteStyle.None if not specified
QuoteStyleFinal = #"Quote Style" ?? QuoteStyle.None,
// 2. PROCESS TEXT FILE
// Return a list of lines of text
Lines = Lines.FromBinary(#"File Contents", null, null, EncodingFinal),
// Take a sample of the lines
LinesSample = List.FirstN(Lines, #"Sample Rows" ?? (each true)),
// Return the maximum number of columns across all sampled lines
MaxColumns = List.Max(
List.Transform(
LinesSample,
each List.Count(Splitter.SplitTextByDelimiter(DelimiterFinal)(_))
)
),
// Read the file using Csv.Document
Source = Csv.Document(
#"File Contents",
[
Delimiter = DelimiterFinal,
Columns = MaxColumns,
Encoding = EncodingFinal,
QuoteStyle = QuoteStyleFinal
]
)
in
Source,
documentation = [
Documentation.Name = "CSVDocumentVariableColumns",
Documentation.LongDescription
= "Wrapper for Csv.Document that determines the required number of columns based on all rows or a sample of rows"
]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
Then in your code, replace Csv.Document calls like this:
= Csv.Document(Parameter1,[Delimiter=" ", Encoding=1254, QuoteStyle=QuoteStyle.None])
with this:
= CsvDocumentVariableColumns(Parameter1)
Parameter1 in your original code was (I assume) the result of a function returning binary file contents (may have been auto-generated).
The CsvDocumentVariableColumns function takes at minimum one parameter File Contents (type binary). You can optionally specify Sample Rows, Delimiter, Encoding and Quote Style, but these currently default to your values from above.
Regarding connecting to SharePoint folders, yes you can definitely do that 🙂
There are various articles/guides out there.
Regards
