Splitting Column values

[dommyw277]

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

[dommyw277]

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.

[MasonMA]

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 

[Shubham_rai955]

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.​

[dommyw277]

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

[FBergamaschi]

Can you explain what outcome you want to get?