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

[Praful_Potphode]

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

[Nabha-Ahmed]

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 

[dommyw277]

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]

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]

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

[Ashish_Mathur]

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

[dommyw277]

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

[Ashish_Mathur]

I really do not know what error you are committing.

[V-yubandi-msft]

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.

[dommyw277]

Hi, the column just says "Error"

[V-yubandi-msft]

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.

[dommyw277]

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]

[V-yubandi-msft]

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

[dommyw277]

Sorry but i dont understand your reply.

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

[V-yubandi-msft]

Hi @dommyw277 ,

Thank you for sharing your query it helps make the issue clearer. The syntax error you encountered is caused by a few formatting issues in the M code.

1. There is a missing comma after the #"Changed Type1" step.

2. The Added Custom step should reference the previous step (#"Changed Type1") instead of Source.

3. The in statement should return the final step in the query.

Below is the corrected version with these changes made.

#"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(
    #"Changed Type1",
    "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
#"Removed Columns"

Once these small fixes are applied, the syntax error should be resolved. If anything still looks off, please let us know what error message you see next.

Regards,

Yugandhar.

[dommyw277]

thnak you but I get this error:

Expression.SyntaxError: Invalid identifier.

[V-yubandi-msft]

Thank you for the update.
The Expression.SyntaxError: Invalid identifier message usually indicates a small formatting issue in the M code, such as

1. A missing comma or parenthesis, or

2. A step name that doesn’t match the one used in the in statement.

Power Query normally highlights the problematic line in red. Could you please share a screenshot of the line that Power Query marks as the error.

Once we can see the highlighted line, it will be easier to identify the solution.

Regards,

Yugandhar.

[dommyw277]

[V-yubandi-msft · Accepted solution]

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]

Amazing thank you, Merry Christmas!  🙂