LSTA Program Evaluations Export for Tableau

From LibWizard, export all submissions using the Date of the program (not the submitted data as a filter). eg. July 1, 204 to June 30, 2025.

Open the .csv file in Excel.

Clean up the inconsistent program date and program title fields by using filtering to match the mismatches and copy/paste consistent titles for each program. Save this .csv file. The more time you spend cleaning the .csv file, the less work later.

  1. Launch Microsoft Excel and open a blank workbook.
  2. Click Data -> Get from Text/CSV and open the .csv file you created earlier.
  3. Click Transform Data
  4. Add an index column via Add Column -> Index Column. (I like to start it at 10 because I don’t like less than 2-digit numbers)
  5. Add a custom column named “PrimaryKey” that concatenates index+date+program titles first 10 characters. Here is the code:
    = Text.From([Index]) & “_” & Date.ToText([Date], “yyyyMMdd”) & “_” & Text.Start([Program Title], 10)
  6.  Add a custom column name “SecondaryKey” that concatenates only date+program titles first 10 characters. Here is the code:
    = Date.ToText([Date], “yyyyMMdd”) & “_” & Text.Start([Program Title], 10
  7. Right-click the data file in the Queries column -> Duplicate
  8. Rename the copy “Results” and click to make it the active query
  9. CTRL+click the PrimaryKey and “As a result of attending this session” columns. Right-click one of the selected columns and choose “Remove other columns
  10. Left drag the PrimaryKey column to the beginning
  11. Select the “As a result” column. From the Transform ribbon, click Split Column -> By Delimiter
  12. Choose Custom and enter a comma followed by a space AND under Advanced Options, choose Split into Rows (this handles cases where people didn’t answer all four of the questions and things would get put into the wrong columns due to the null responses) Click OK
  13. Select the “As a result column” again then Transform -> Split Column -> By Delimiter -> Custom. This time enter a space then a dash then a space. Do not change the Advanced Options, it will default to columns. Click OK
  14. You should now have a column with the questions repeated and a column with the responses. Rename these columns “Questions” and “Responses” by double-clicking the column header
  15. Add a column called “Result Values” that will replace the Responses text with numbers. Here is the code:
    = each let
    cleaned = Text.Trim([Responses]),
    likert = {“Strongly Disagree”, “Disagree”, “Neither Agree nor Disagree”, “Agree”, “Strongly Agree”},
    score = List.PositionOf(likert, cleaned) + 1
    in
    if score > 0 then score else null

16. Create another duplicate of the source .csv file (right-click-> duplicate) and name it Ratings. Select the PrimaryKey and Ratings column; right-click delete other columns.

17. Repeat Step 15 to create a “Ratings Values” column.

18. Create an Organizations table by right-clicking the source .csv -> Duplicate. Select only the Secondary key and Organization types columns and remove the others.

Scroll to Top