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;

LinkIDProduct.IdProductParent
11Tree 
12Apples1
13Big Leaves2
210Plane 
212Fuel 
214Wings10
216Flooring10
220Tail12
222Seats14
224Food14
224Staff20

 

And this is the result that I would like to get to;

LinkIDTopMiddleBottom
1TreeApplesBig Leaves
2Plane;FuelWings;Flooring;TailSeats;Food;Staff


It is best if I have this in the Power Query.

 

Thanks in advance!!

Need Help

4 Replies

  • 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