Forum Discussion
Splitting Column values
Hi dommyw277 ,
Thanks for the screenshot that really helps. As per my guess, the Invalid identifier error is coming from the way the column name Inc/SR is being referenced. Because the column name contains a slash (/), Power Query requires the #"" format.
So anywhere you have- [Inc/SR]
please change it to - [#"Inc/SR"]And in your filter step
each [#"Inc/SR"] <> null and [#"Inc/SR"] <> ""Once the column is referenced with [#"...."] , the syntax error should be resolved.
Hope this helps please give it a try. If anything still comes up, feel free to share another screenshot and we can take a look.
Hi, apologies i have just been told this the actual data:
Password Resets
SR-01
SR-02
SR-03
Information Requests
SR-05
INC-01
INC-02
Application faults
INC-03
SR-06
There are lots of other categories but the incidents and Requests always starts INC or SR
Apologies
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Inc/SR", each if Text.StartsWith([Remarks],"SR-",Comparer.OrdinalIgnoreCase) or Text.StartsWith([Remarks],"INC-",Comparer.OrdinalIgnoreCase) then [Remarks] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Category", each if [#"Inc/SR"]=null then [Remarks] 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",{"Remarks"})
in
#"Removed Columns"Hope this helps.
Getting only errors at the moment. What parts do i need to add my info? Just the table part?
Give a name to the Table as Data. Then the code will work.
Renamed the table to Data as per your screenshot but still getting an error only in the column