Forum Discussion

dommyw277's avatar
dommyw277
Helper V
8 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
    7 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.

25 Replies

  • dommyw277's avatar
    dommyw277
    Helper V
    8 months ago

    thank you. I want to put all the categories in one column and the SR plus INC in another column 

     

  • Shubham_rai955's avatar
    Shubham_rai955
    Super User
    8 months ago

    In Power Query Editor, add an Index column, then use a custom function to split grouped values into separate Category/Item rows.​

    Solution Steps

    • Add Index: Select column > Add Column > Index Column (from 0).​

    • Custom Column with M formula:

     
     
    if Text.StartsWith([Column], "Category") then { [Column], null } else { Text.BeforeDelimiter(Text.From([Column]), "Category" & Number.From(Text.PositionOf(Text.From([Column]), "Category")), [Column] }

    • Expand List: Expand the new list column to rows (Attribute=Category, Value=Item).​​

    Advanced M Code (Advanced Editor)

    Replace your query step with:

     
     
    let Source = ..., #"Added Index" = Table.AddIndexColumn(Source, "Index",0,1), #"Split Rows" = Table.ExpandListColumn( Table.AddColumn(#"Added Index", "Split", each List.Generate( () => [i=0, cat=Text.Select([Column], each Text.Contains("Category"))], each [i] < List.Count(Text.Split([Column],"#(lf)")), each [i=[i]+1] ) ), "Split" )

    Filter nulls, remove Index.​

  • MasonMA's avatar
    MasonMA
    Super User
    8 months ago

    Hi, 

     

    You'd need 3 major Power Query UI steps. For your sample data, first add a column, 

    Fill down 'Custom' Column. 

    Filter out 'Category' in Column1 

    The rest would be renaming, reordering etc. 

     

    Same logic for your new data dommyw277 

  • dommyw277's avatar
    dommyw277
    Helper V
    8 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
      Super User
      8 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
        Helper V
        8 months ago

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

  • Nabha-Ahmed's avatar
    Nabha-Ahmed
    Super User
    8 months ago

    Hi dommyw277 

    Create a “Category” column using Power Query

    Step 1: Open Power Query

    In Power BI Desktop → Transform Data

     

    ---

    Step 2: Identify category rows

    Add a conditional column or mark rows starting with “Category”:


    = Table.AddColumn(PreviousStep, "IsCategory", each if Text.StartsWith([Column1], "Category") then 1 else 0)


    ---

    Step 3: Fill down the category

    Select the Column1 (or a new “Category” column)

    Transform → Fill → Down

    This will copy the last Category value to the rows below until the next category appears.

     

    ---

    Step 4: Filter out category rows (optional)

    If you only want codes with their category, filter IsCategory = 0.

     

    ---

    Step 5: Rename columns

    Column1 → Code

    Filled-down column → Category


    Your final table:

    Category Code

    Category1 SR-01
    Category1 SR-02
    Category1 SR-03
    Category2 SR-05
    Category2 INC-01
    Category2 INC-02
    Category3 INC-03
    Category3 SR-06

    Please Mark [as a solution] if this help you 

    To help others 

    Best regards 

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    8 months ago

    Hi dommyw277 ,

     

    try Sample PBIX and let us know.

    M QUery code looks like below:

    let
    Source = Table.FromRows(
        {
            {"Category1"},
            {"SR-01"},
            {"SR-02"},
            {"SR-03"},
            {"Category 2"},
            {"SR-05"},
            {"INC-01"},
            {"INC-02"},
            {"Category 3"},
            {"INC-03"},
            {"SR-06"}
        },
        type table [Column1 = text]
    ),//replace this step with your source step
    AddedCategory = Table.AddColumn(Source, "Category", each 
        if Text.Contains([Column1], "-") then null else [Column1]
    ),
    FilledDown = Table.FillDown(AddedCategory, {"Category"}),
    FilteredRows = Table.SelectRows(FilledDown, each Text.Contains([Column1], "-")),
    RenamedColumns = Table.RenameColumns(FilteredRows, {{"Column1", "Code"}})
in
    RenamedColumns

    Please give kudos or mark it as solution once confirmed.

     

    Thanks and Regards,

    Praful