let
    Source = Excel.Workbook(File.Contents("C:\Users\Robert\Downloads\Assignment_Detail_Tracking_TEST.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Contractor", type text}, {"ID", Int64.Type}, {"Event Date", type date}, {"Detail Start Date", type date}, {"Detail End Date", type date}, {"Amendment Event Type", type text}, {"Amendment Reason", type text}, {"Amendment Type", type text}, {"Comment", type text}, {"Bill Rate", type number}, {"Current Start Date", type date}, {"Supplier", type text}, {"Hiring Manager", type text}, {"Tax Work Location", type text}, {"VMO", type text}, {"Detail Status", type text}, {"Longevity In Days", Int64.Type}, {"Status", type text}, {"Sub Status", type text}, {"Labor Category", type text}}),
    #"Multi Sort on Contractor Ascending and Detail Start Date Descending" = Table.Sort(#"Changed Type",{{"Contractor", Order.Ascending}, {"Detail Start Date", Order.Descending}}),
    #"Change Detail Start Date to Text" = Table.TransformColumnTypes(#"Multi Sort on Contractor Ascending and Detail Start Date Descending",{{"Detail Start Date", type text}}),
    #"Extract Building Number from Tax Work Location" = Table.AddColumn(#"Change Detail Start Date to Text", "Text Before Delimiter", each Text.BeforeDelimiter([Tax Work Location], " -"), type text),
    #"Name Building Column" = Table.RenameColumns(#"Extract Building Number from Tax Work Location",{{"Text Before Delimiter", "Building"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Name Building Column",{"ID", "Event Date", "Detail End Date", "Amendment Reason", "Amendment Type", "Comment", "Bill Rate", "Current Start Date", "Hiring Manager", "VMO", "Detail Status", "Status", "Sub Status", "Longevity In Days", "Labor Category", "Tax Work Location"}),
    #"Include Only Creation and Termination" = Table.SelectRows(#"Removed Columns", each Text.Contains([Amendment Event Type], "Termination") or Text.Contains([Amendment Event Type], "Creation")),
    #"Added Index" = Table.AddIndexColumn(#"Include Only Creation and Termination", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Date Range", each if #"Added Index"[Contractor] = #"Added Index"{[Index]   1}[Contractor] and #"Added Index"[Supplier] = #"Added Index"{[Index]   1}[Supplier] and #"Added Index"[Building] = #"Added Index"{[Index]   1}[Building] and Text.Contains(#"Added Index"[Amendment Event Type], "Termination") and Text.Contains(#"Added Index"{[Index]   1}[Amendment Event Type], "Creation") then #"Added Index"{[Index]   1}[Detail Start Date] & " - " & #"Added Index"[Detail Start Date] else if #"Added Index"[Index] > 0 and #"Added Index"[Contractor] = #"Added Index"{[Index] - 1}[Contractor] and #"Added Index"[Supplier] = #"Added Index"{[Index] - 1}[Supplier] and #"Added Index"[Building] = #"Added Index"{[Index] - 1}[Building] and Text.Contains(#"Added Index"[Amendment Event Type], "Creation") and Text.Contains(#"Added Index"{[Index] - 1}[Amendment Event Type], "Termination") then #"Added Index"[Detail Start Date] & " - " & #"Added Index"{[Index] - 1}[Detail Start Date] else if Text.Contains(#"Added Index"[Amendment Event Type], "Creation") then #"Added Index"[Detail Start Date] & " - " & DateTime.LocalNow() else null),
    #"Date Range" = #"Added Custom"{0}[Date Range]
in
    #"Date Range"

I am fairly new to Power Query but am working with a data set with employee assignment details. I have imported the spreadsheet and formatted the data such that for every employee there will be multiple rows per employee (one for hiring date and another for termination date if applicable), and I am hoping to find some way to consolidate these rows. I decided to create a custom column that will make sure that for any given row, if the next/previous row has the same employee, temp agency, and building are the same and there is a creation and termination date, then a date range will be created.

I am running into errors with creating the custom column due to various columns being treated as lists. Converting the data type of the column doesn't work, and other threads that have a similar error seem to incorporate data types (numbers, text) at the level of a value whereas tables, records and lists are different. I have read documentation but am still confused on how I should proceed. I have made sure the columns are of the appropriate data type for my formula, and I'm not getting any syntax errors. The error is specifically happening with

else if #"Added Index"[Index] > 0

on line 13 since Index is being treated as a list, but even if I remove that, I get similar errors for other columns. Perhaps I have just missed something important when loading excel data but would appreciate any help.

CodePudding user response：

Well, to get you started, you can't do this, which compares a list to an item in a list:

 if #"Added Index"[Contractor] = #"Added Index"{[Index]   1}[Contractor] 

you would need to do

if #"Added Index"{[Index]}[Contractor] = #"Added Index"{[Index]   1}[Contractor] 

and similar

You might also have better luck writing and testing your formula in pieces