Splitting Column values

Amazing thank you, Merry Christmas!  🙂

Sorry but i dont understand your reply.

The column is called RequestID, im not sure what i add where in the advanced editor? 

This is what i have minus the top 2 lines which contain info i cant post eg source of files:

#"Removed Top Rows" = Table.Skip(#"Changed Type",5),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"RequestID", type text}, {"Request Mode", type text}, {"Technician", type text}, {"Created Time", type datetime}, {"Completed Time", type datetime}, {"Subject", type text}, {"Request Type", type text}})
#"Added Custom" = Table.AddColumn(Source, "Inc/SR", each if Text.StartsWith([RequestID],"SR-",Comparer.OrdinalIgnoreCase) or Text.StartsWith([RequestID],"INC-",Comparer.OrdinalIgnoreCase) then [RequestID] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Category", each if [#"Inc/SR"]=null then [RequestID] else null),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Category"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each [#"Inc/SR"] <> null and [#"Inc/SR"] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"RequestID"})
in
#"Changed Type1"

I then get an error:

Expression.SyntaxError: Token ',' expected.

Hi dommyw277 ,

Thank you for sharing your query it helps make the issue clearer. The syntax error you encountered is caused by a few formatting issues in the M code.

1. There is a missing comma after the #"Changed Type1" step.

2. The Added Custom step should reference the previous step (#"Changed Type1") instead of Source.

3. The in statement should return the final step in the query.

Below is the corrected version with these changes made.

#"Removed Top Rows" = Table.Skip(#"Changed Type",5),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
        {"RequestID", type text},
        {"Request Mode", type text},
        {"Technician", type text},
        {"Created Time", type datetime},
        {"Completed Time", type datetime},
        {"Subject", type text},
        {"Request Type", type text}
    }
),
#"Added Custom" = Table.AddColumn(
    #"Changed Type1",
    "Inc/SR",
    each if Text.StartsWith([RequestID], "SR-", Comparer.OrdinalIgnoreCase)
        or Text.StartsWith([RequestID], "INC-", Comparer.OrdinalIgnoreCase)
        then [RequestID] else null
),
#"Added Custom1" = Table.AddColumn(
    #"Added Custom",
    "Category",
    each if [Inc/SR] = null then [RequestID] else null
),
#"Filled Down" = Table.FillDown(#"Added Custom1", {"Category"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Inc/SR] <> null and [Inc/SR] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows", {"RequestID"})
in
#"Removed Columns"

Once these small fixes are applied, the syntax error should be resolved. If anything still looks off, please let us know what error message you see next.

Regards,

Yugandhar.

thnak you but I get this error:

Expression.SyntaxError: Invalid identifier.

Thank you for the update.
The Expression.SyntaxError: Invalid identifier message usually indicates a small formatting issue in the M code, such as

1. A missing comma or parenthesis, or

2. A step name that doesn’t match the one used in the in statement.

Power Query normally highlights the problematic line in red. Could you please share a screenshot of the line that Power Query marks as the error.

Once we can see the highlighted line, it will be easier to identify the solution.

Regards,

Yugandhar.