Forum Discussion
Split the combined value into two separate lines
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 |
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:
Hope this can help.
Best Regards,
Giotto Zhi
2 Replies
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
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:
Hope this can help.
Best Regards,
Giotto Zhi
