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.

Shubham_rai955's avatar
Shubham_rai955
Icon for Super User rankSuper User
9 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.​