DAX for splitting value in a single column to rows in a new table

[arbelyoav]

Hi,

I have a calculated table (table 1) that has a single column which contain values with ";" delimiter. 
I would like to create a new table (table 2), based on table 1, that will split the delimited values into rows per each delimited value.

-Final result is a table which contain the same columns as table 1 of course with more rows.
-Need to be in DAX (not Power Query)

Example:
Table 1:

Part number	PCB PN	Part Number description
SFG000123	PCB000555;PCB000987;PCB000123	Disk

 
Table 2:

Part Number	PCB PN	Part Number description
SFG000123	PCB000555	Disk
SFG000123	PCB000987	Disk
SFG000123	PCB000123	Disk

[arbelyoav]

Works!!
Thanks

[kameronyork · Accepted solution]

I hope this suggestion is helpful for you 🙂

I first recreated the data you provided.  In the example you gave there were three PCB PNs in the column.  I split the column (using text functions) into three columns.

Here is the code I used for each column:

PCB PN 1 =   LEFT( [PCB PN], FIND(";", [PCB PN]) - 1)

PCB PN 2 = 

IF( LEN([PCB PN]) - LEN( SUBSTITUTE([PCB PN], ";", "")) >= 1,
    MID( [PCB PN], FIND(";", [PCB PN], 1) + 1, LEN([PCB PN 1])),
    BLANK())

// The condition will remove all ;'s and compare to the original.
// This will ensure there is at least one semicolon and thus two values available.

PCB PN 3 = 

IF( LEN([PCB PN]) - LEN( SUBSTITUTE([PCB PN], ";", "")) = 2,
    RIGHT( [PCB PN], LEN([PCB PN 1])),
    BLANK())

// The condition will remove all ;'s and compare to the original.
// This will ensure there are two semicolons and thus three values.

You should create enough columns to satisfy the most values that will be delimited.  If there will never be more than three then this amount will work for you.


I then created a custom table with the number of values that will be pulled from the delimited column.  This is to allow me to use the CROSSJOIN function to manually create a pivoted version of the table.  Using CROSSJOIN will result in a table that looks like this:

Then I used lookup functions to switch which column from the original table is being returned, resulting in a table that looks like this:

Here is the code I used:

PCB PN Lookup = 

SWITCH( [Value],
    1, LOOKUPVALUE( 'Table'[PCB PN 1], 'Table'[Part Number], [Part Number]),
    2, LOOKUPVALUE( 'Table'[PCB PN 2], 'Table'[Part Number], [Part Number]),
    3, LOOKUPVALUE( 'Table'[PCB PN 3], 'Table'[Part Number], [Part Number])
)

// The switch function will change which column will be returned.

Part Number Desc Lookup = 

SWITCH( [Value],
    1, LOOKUPVALUE( 'Table'[Part Number Description], 'Table'[Part Number], [Part Number]),
    2, LOOKUPVALUE( 'Table'[Part Number Description], 'Table'[Part Number], [Part Number]),
    3, LOOKUPVALUE( 'Table'[Part Number Description], 'Table'[Part Number], [Part Number])
)

// This continues to use switch but can be done in one line using lookup if the Description will always be the same.

I tested the solution with more rows to make sure it will work and it seems it will!


I hope this suggestion helps!  If it does, please mark this as the solution 🙂