Forum Discussion

dommyw277's avatar
dommyw277
Icon for Helper V rankHelper V
9 months ago
Solved

Splitting Column values

Hi, how would i split a column like this:   Category1 SR-01 SR-02 SR-03 Category 2 SR-05 INC-01 INC-02 Category 3 INC-03 SR-06 etc etc
Need Help
  • V-yubandi-msft's avatar
    V-yubandi-msft
    8 months ago

    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.

dommyw277's avatar
dommyw277
Icon for Helper V rankHelper V
9 months ago

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

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    9 months ago

    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.

     

    • dommyw277's avatar
      dommyw277
      Icon for Helper V rankHelper V
      8 months ago

      Getting only errors at the moment. What parts do i need to add my info? Just the table part? 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 months ago

        Give a name to the Table as Data.  Then the code will work.