gnarkill78
New Member
Hello all,
I am using power query to extract document versions from the Objective software (records management software), build a table containing the versionList then combine the tables from each version to build a historic table. The following snippet of code works perfectly for *.xlsx files however I cannot for the life of me get it to work on *.csv documents.
So basically, it iterates through versions, starting at 1.0 and incrementing by 1 to test for valid versions, then builds the versionTables.
I've tried modifying Excel.Workbook to Csv.Document and adding ,[Delimiter=",", Columns=36, Encoding=1252, QuoteStyle=QuoteStyle.Csv] into the code however I just continue to get errors.
Would love any suggestions on where to go from here.
Thanks in advance.
I am using power query to extract document versions from the Objective software (records management software), build a table containing the versionList then combine the tables from each version to build a historic table. The following snippet of code works perfectly for *.xlsx files however I cannot for the life of me get it to work on *.csv documents.
Code:
let
baseURL = "https://some/location/document/versions/",
startingVersion = 1,
versionsList = List.Generate(
() => startingVersion,
each
let
workbook = try Excel.Workbook(Web.Contents(baseURL & Number.ToText(_,"0.0")), null, true) otherwise null,
condition = workbook <> null
in
condition,
each _ + 1
),
versionTables = List.Transform(versionsList, each try Table.Skip(Excel.Workbook(Web.Contents(baseURL & Number.ToText(_,"0.0")), null, true){[Item="Sheet1",Kind="Sheet"]}[Data], 1) otherwise null),
filteredTables = List.RemoveNulls(versionTables),
finalTable = if List.IsEmpty(filteredTables) then null else Table.Combine(filteredTables)
in
#"finalTable"
So basically, it iterates through versions, starting at 1.0 and incrementing by 1 to test for valid versions, then builds the versionTables.
I've tried modifying Excel.Workbook to Csv.Document and adding ,[Delimiter=",", Columns=36, Encoding=1252, QuoteStyle=QuoteStyle.Csv] into the code however I just continue to get errors.
Would love any suggestions on where to go from here.
Thanks in advance.