Best way to separate data in one Column

[JayTG123]

Hi all,

I am looking for the best way to seperate the works location (screenshot below) to allow me to sum each category together. 

Split column is a possibility but I wanted to know if there is an easier way perhaps? 

What I am after is to calculate everything in the footway, carriageway, verge, etc. (all individually)

Many thanks in advance.

Jay

[Anonymous · Accepted solution]

Hi @JayTG123 ,

You could create a new table by using DAX to split string by delimiter.

new_table = 
VAR tmp =
    ADDCOLUMNS (
        GENERATE (
            'table',
            VAR TokenCount =
                PATHLENGTH ( SUBSTITUTE ('table'[works location], ",", "|" ) )
            RETURN
                GENERATESERIES ( 1, TokenCount )
        ),
        "Split", PATHITEM ( SUBSTITUTE ( 'table'[works location], ",", "|" ), [Value] )
    )
RETURN
  SELECTCOLUMNS(tmp, "works location", [Split], "sales", 'table'[sales])

Best Regards,

Jay

[JayTG123]

Thanks @AilleryO The data will change month by month but the calculation will be the same. Just a basic sum.

Have a good split indeed lol.

Jay

[AilleryO]

Hi,

I think like you that the split columns on the , will be the best.

For your further analysis the question is, will it be better to split in columns or lines... ?

Depends on your data and future calculation.

If you split in lines, you might duplicate some values which could be interesting or not.

Have a good split 😉