Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Split 1 Column to 3 Column based on parent

Hi, I have been looking at a problem that I have and I need to convert one column to multiple columns based on their parent.   Here is the data that I have; LinkID Product.Id Product Par...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
6 years ago

hi Anonymous 

For your case, you could try this way as below:

Step1:

Add a conditional column that which row is Top, Middle, Bottom by this formula like below:

Type = var _toptable=CALCULATETABLE(VALUES('Table'[Product.Id]),ALLEXCEPT('Table','Table'[LinkID]),'Table'[Parent]=BLANK()) return
var _Middletable=CALCULATETABLE(VALUES('Table'[Product.Id]),FILTER(ALLEXCEPT('Table','Table'[LinkID]),'Table'[Parent] in _toptable) ) return
var _Bottomtable=CALCULATETABLE(VALUES('Table'[Product.Id]),FILTER(ALLEXCEPT('Table','Table'[LinkID]),'Table'[Parent] in _Middletable) ) return
IF('Table'[Product.Id] in _toptable,"Top",IF('Table'[Product.Id] in _Middletable,"Middle",IF('Table'[Product.Id] in _Bottomtable,"Bottom")))

Then use this formula to create a New table

New table = 
SUMMARIZE (
    'Table',
    'Table'[LinkID],
    "Top", CALCULATE (CONCATENATEX ( VALUES ( 'Table'[Product] ), [Product], "," ), FILTER ( 'Table', 'Table'[Type] = "Top" )),
    "Middle", CALCULATE (CONCATENATEX ( VALUES ( 'Table'[Product] ), [Product], "," ),FILTER ( 'Table', 'Table'[Type] = "Middle" )),
    "Bottom", CALCULATE (CONCATENATEX ( VALUES ( 'Table'[Product] ), [Product], "," ),FILTER ( 'Table', 'Table'[Type] = "Bottom" ))
)

Result:

and here is sample pbix file, please try it.

 

Regards,

Lin

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi v-lili6-msft 

     

    All looking good, I have started to follow your steps but when I am creating the custom column I get the below error;

     
     

     

    Thank you for your help

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Great, yes I see now thank you very much and it all worked