Display Multiple level data

[Anonymous]

Hello all colleagues,

I have a data like this table (SQL)

When a Component has childrent, the price is zero.

Level	Parent	Component	Price
1	Computer	Monitor	0
1	Computer	Mouse	0
1	Computer	Keyboard	0
1	Computer	Cable HDMI	22
2	Monitor	LCD Panel	28
2	Monitor	Monitor Frame	84
2	Monitor	Monitor Mainboard	0
2	Mouse	Button	53
2	Mouse	Scroll	89
2	Mouse	Laser	15
2	Keyboard	Button 1	50
2	Keyboard	Button 2	46
2	Keyboard	Button 3	21
2	Keyboard	Button 4	11
3	Monitor Mainboard	Chip M	15
3	Monitor Mainboard	Ram M	20
3	Monitor Mainboard	A	68
3	Monitor Mainboard	B	33

The expeted output is somethings like this on Power BI

Do you have any solution to tranform data to solve this problem ?

Now I have

- Raw data (like the table above) in Database (Synapse);  data will load to data Model (Azure Analysis Service). And Power BI read data from Azure Analysis Service

- I have multiple level (not only 3) so I cannot create a table with multiple column to create hierarchy as normal.

I need an ideal to automatically display the data as expeted ( above picture)

Thank you

[Smauro · Accepted solution]

Hi @Anonymous 

You could start with this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDBC4IwGMX/lbFzB91U7JiTKHIQdRQPswYJ6kTnof++fRqlqDt88Lb3Y3vvS1Ps4h1mqmp6LVsjuaoLrUA5ONut2H0nt8yLfOdKtM8tn4m8lOgU87M5EDIgZPZnwmJ0FbUsAQhXgK9Cx1ZUkCP0LBAXRT0PRCYVol5rVRvh04V3f7SqhBDhfuElohvauP7PmhQfX0XQ3HcsANx6gQWgsALXAniQYQToRmn2KhrE/1m3uJuoBow4VuxgJgitSGSGmn1mHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Level = _t, Parent = _t, Component = _t, Price = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}}),
    
    expandNames = (k, n) => List.Combine(List.Transform({n-k+1..n}, each { Text.From(_) & ".Component", Text.From(_) & ".Price" } )),
    joinK = (tablelist, statetable, k, n) => Table.NestedJoin(tablelist{k}, Text.From(n-k) & ".Component", statetable, Text.From(n-k+1) & ".Parent", "data", JoinKind.LeftOuter),
    recordNames = (rec, series) => List.Accumulate(List.RemoveFirstN(series,1), Record.Field(rec, Text.From(List.Max(series)) & ".Component"), (s, c) => s??Record.Field(rec, Text.From(c) & ".Component")),

    maxLevel = List.Max(PreviousStep[Level]),
    levels = List.Numbers(maxLevel, maxLevel, -1),
    tables = List.Transform(levels, (item) => Table.PrefixColumns(Table.SelectRows(PreviousStep, each [Level] = item), Text.From(item) ) ),

    #"Combine Levels" = List.Accumulate({1..(maxLevel-1)}, tables{0}, (statetable, c) => Table.ExpandTableColumn( joinK(tables, statetable, c, maxLevel) , "data", expandNames(c, maxLevel), expandNames(c, maxLevel))),
    #"Combine Prices" = Table.CombineColumns(#"Combine Levels", List.Transform(levels, each Text.From(_)&".Price"), (x) as number => Number.From(List.Max(x)), "Price"),
    #"Replace null Components" = Table.ReplaceValue(#"Combine Prices", null, (r) => recordNames(r, levels), Replacer.ReplaceValue, List.Transform(levels, each Text.From(_) & ".Component") )
in
    #"Replace null Components"

To see it with your data, replace PreviousStep with your last step's name.

Best,

Spyros

[Anonymous]

I don't fully understand your code, but it's running well. Great job!

[Anonymous]

I think this is the answer I need. I will try it and respond the result

[Jimmy801]

Hello @Anonymous 

you can do this in DAX. Add 2 new columns to your data with this formula

NewParent = 
IF (
COUNTROWS (
FILTER ( 'Table', 'Table'[Component] = EARLIER ( 'Table'[Parent] ) )
)
= 0,
'Table'[Component],
'Table'[Parent]
)

Path = PATH (
'Table'[Component],
'Table'[NewParent]
)

then add new columns as many levels you have. Here the code for level 1

Level1 = 
LOOKUPVALUE (
'Table'[Component],
'Table'[Component], PATHITEM ('Table'[Path], 1 )
)

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

But in this case, you still manualy add the level. I dont know how many level will I have. Thanks

[Jimmy801]

Hello @Anonymous 

just add enough new columns. Power BI to add a hierarchy needs new columns. 

BR

Jimmy