• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting version list into table

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.

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.
 
Back
Top