topic Re: Splitting Column values in Desktop

Splitting Column values

dommyw277 — Fri, 05 Dec 2025 15:45:42 GMT

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

Re: Splitting Column values

FBergamaschi — Fri, 05 Dec 2025 15:47:20 GMT

Can you explain what outcome you want to get?

Re: Splitting Column values

dommyw277 — Fri, 05 Dec 2025 15:53:18 GMT

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

Re: Splitting Column values

Shubham_rai955 — Fri, 05 Dec 2025 15:53:46 GMT

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.

Re: Splitting Column values

MasonMA — Fri, 05 Dec 2025 16:23:09 GMT

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

Re: Splitting Column values

dommyw277 — Fri, 05 Dec 2025 16:19:26 GMT

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

Re: Splitting Column values

Ashish_Mathur — Fri, 05 Dec 2025 23:58:13 GMT

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.

Re: Splitting Column values

Nabha-Ahmed — Sat, 06 Dec 2025 00:03:22 GMT

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

Re: Splitting Column values

Praful_Potphode — Sat, 06 Dec 2025 07:21:47 GMT

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

Re: Splitting Column values

dommyw277 — Tue, 09 Dec 2025 10:25:02 GMT

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

Re: Splitting Column values

Ashish_Mathur — Tue, 09 Dec 2025 10:27:59 GMT

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

Re: Splitting Column values

dommyw277 — Tue, 09 Dec 2025 10:49:02 GMT

Renamed the table to Data as per your screenshot but still getting an error only in the column

Re: Splitting Column values

Ashish_Mathur — Tue, 09 Dec 2025 11:11:35 GMT

I really do not know what error you are committing.

Re: Splitting Column values

V-yubandi-msft — Fri, 12 Dec 2025 11:51:51 GMT

Hi dommyw277 ,

We’re not sure yet what specific error you’re seeing after using the code provided by Ashish_Mathur
To help you more accurately, could you please share the exact error message or a screenshot of the error.

This will make it much easier for us to identify the issue and guide you correctly.

Ashish_Mathur , Thanks for your patience and for sharing the details so far.

Re: Splitting Column values

dommyw277 — Fri, 12 Dec 2025 15:12:30 GMT

Hi, the column just says "Error"

Re: Splitting Column values

V-yubandi-msft — Mon, 15 Dec 2025 06:39:12 GMT

Thanks for the update.

When Power Query shows only Error in a column, there’s usually a more detailed message behind it. Could you please click on one of the error cells and share the full error message or a screenshot of it.

That will help us understand exactly what’s going wrong and guide you in the right direction.

Thanks for your patience, happy to help once we have that detail.

Re: Splitting Column values

dommyw277 — Mon, 15 Dec 2025 12:00:44 GMT

So the file is a csv not an excel document so I cahnged the code to below however, i get an error :

let
Source = Csv.Document(){[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"

Error:

Expression.Error: 0 arguments were passed to a function which expects between 1 and 5.
Details:
Pattern=
Arguments=[List]

Re: Splitting Column values

V-yubandi-msft — Tue, 16 Dec 2025 05:15:52 GMT

Hi dommyw277 ,

Thanks for clarifying that the source file is a CSV.

The error you’re seeing is expected with this line:

Source = Csv.Document(){[Name="Data"]}[Content],

Unlike Excel, a CSV file doesn’t contain named tables (such as Data).

Csv.Document must be provided with the file contents, so referencing a table name will result in the error.

0 arguments were passed to a function which expects between 1 and 5

To resolve this, please use the Source step that Power Query automatically generates when you import the CSV file, and then apply the remaining transformation steps (Add Column, Fill Down, Filter, etc.) on top of that source.

Also, please double check the exact column name (for example, Remarks vs Coloum1) and ensure headers are promoted correctly.

Once the source step is corrected, the rest of the logic should work as expected.

Hope this helps..

Re: Splitting Column values

dommyw277 — Tue, 16 Dec 2025 12:27:42 GMT

Sorry but i dont understand your reply.

The column is called RequestID, im not sure what i add where in the advanced editor?

Re: Splitting Column values

dommyw277 — Tue, 16 Dec 2025 14:13:06 GMT

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.