Split the combined value into two separate lines

[DenisSlav]

Hello Community, 

How can I transform the table:

ITEM_LIST	ID	VALUE
XYZ	1	700
ABC | XYZ	2	1000
ABC | QWE | ASD	3	600

Where in DESCRIPTION  list of items with delimeter " | ". To another table with condition EST_VALUE = VALUE / N, where N - count of different item in description:

ITEM	ID	EST_VALUE
XYZ	1	700
ABC	2	500
XYZ	2	500
ABC	3	200
QWE	3	200
ASD	3	200

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, please take following steps:

1)Split [ITEM_LIST] into rows by " | ":

2)After Apply&Close, create a measure as below:

Measure = SUM('Table'[VALUE]) / CALCULATE(COUNTROWS('Table'),FILTER(ALLSELECTED('Table'),'Table'[ID] in FILTERS('Table'[ID])))

3)The result shows:

Here is my test pbix file:

pbix 

Hope this can help.

Best Regards,

Giotto Zhi

[amitchandak]

Please refer : Power query Split by delimiter into rows.

https://community.powerbi.com/t5/Desktop/How-to-split-the-the-Column-into-Multiple-rows/td-p/253361