Split 1 Column to 3 Column based on parent

[Anonymous]

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	Parent
1	1	Tree
1	2	Apples	1
1	3	Big Leaves	2
2	10	Plane
2	12	Fuel
2	14	Wings	10
2	16	Flooring	10
2	20	Tail	12
2	22	Seats	14
2	24	Food	14
2	24	Staff	20

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

LinkID	Top	Middle	Bottom
1	Tree	Apples	Big Leaves
2	Plane;Fuel	Wings;Flooring;Tail	Seats;Food;Staff

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

Thanks in advance!!

[v-lili6-msft]

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]

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

[v-lili6-msft · Accepted solution]

hi @Anonymous 

The formula is for calculated column, not custom column in edit queries.

Please download my sample pbix file and see the details in it.

Regards,

Lin

[Anonymous]

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