How can I get a column to delimit as seperate rows ?

[EaglesTony]

Hi,

  I have the following table:

  Application Code     Owners

  ABC                          EW123, FH902, G903

  DEF                          AG945

  I need to break this out to individual application code/Owners as such to do a merge process

  Application Code     Owners

  ABC                          EW123

  ABC                          FH902

  ABC                          G903

  DEF                          AG945

Thanks

[DataVitalizer]

Hi @EaglesTony 

Follow these steps:

1. Right-click the Owners column
2. Select Split Column → By Delimiter
3. In the delimiter selection, choose Comma (",")
4. Under Split Options, choose Split into Rows

Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡

🟩 Follow me on LinkedIn

[jaineshp · Accepted solution]

Hey @EaglesTony,

Based on your requirement to split comma-separated owner values into individual rows, here are the recommended approaches:

Method 1: Using Power Query Editor

1. Select the target table in Power BI Desktop and click "Transform data" to open Power Query Editor
2. Navigate to the Owners column and select it by clicking on the column header
3. Access the Split Column feature by going to Home tab → Split Column → By Delimiter
4. Configure delimiter settings:
   • Choose "Comma" as the delimiter
   • Select "Each occurrence of the delimiter" option
   • Choose "Rows" instead of "Columns" in the advanced options
5. Clean up the data by selecting the Owners column and applying Transform → Trim to remove any extra spaces
6. Apply changes by clicking "Close & Apply" to return to the main Power BI interface

Method 2: Using DAX

If you prefer a calculated table approach:

ExpandedTable =
ADDCOLUMNS(
SELECTCOLUMNS(
GENERATESERIES(1, MAXX(YourTable, LEN(YourTable[Owners]) - LEN(SUBSTITUTE(YourTable[Owners], ",", "")) + 1)),
"RowNumber", [Value]
),
"Application Code",
LOOKUPVALUE(
YourTable[Application Code],
YourTable[Application Code],
YourTable[Application Code]
),
"Owners",
TRIM(
PATHITEM(
SUBSTITUTE(YourTable[Owners], ",", "|"),
[RowNumber]
)
)
)

The Power Query method (Method 1) is strongly recommended as it provides the most robust and maintainable solution for your merge process requirements.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[EaglesTony]

Thank you!...Exactly what I needed and I did use option 1, since I have to merge data to it.

[jaineshp]

Sounds good, Happy to contribute!