cancel
Showing results for
Did you mean:
Regular Visitor

## Re: Bill of Material Totals

Hi Imke

My table has only these 3 columns. I did not understand how to convert this a parent child form. Can you please elaborate?

Level Part Qty

1A1

2B5

3C3

4D2

2E1

3F1

Super User

## Re: Bill of Material Totals

Sorry, I didn't read your thread throroughly enough. What would the result for E and F be?: Each 1?

So the logic would be to roll everything up towards 1 by multiplying it?

Are the Level/Part-combinations exclusive?

(This use case looks a bit different to the parent-child-hierarchy from my example).

Proud to be a Datanaut!

Imke Feldmann

Regular Visitor

## Re: Bill of Material Totals

I got this to work partially. it breaks if the depth of the total number of levels changes. I still need help with that step.

Multilevel totals

Regular Visitor

## Re: Bill of Material Totals

yes E and F would be one. Level/part combinations are not exclusive. I have posted a partial solution. Need help with a step where multiple columns are multiplied and the number of columns varies due to changing levels. please review the excel file i shared as a reply to my question.. there is a link.

Super User

## Re: Bill of Material Totals

thanks, but your sample is not clear to me. How would the results be if the first row: Level:1 Part:A would be 10 instead of 1? Would this impact the result of the yellow figures?

Proud to be a Datanaut!

Imke Feldmann

Regular Visitor

## Re: Bill of Material Totals

Yes it would impact. If the parent level increases then all children get multiplied by it.

How can I deal with the multiplication of varying number of columns generated by pivoting the levels?

Super User

## Re: Bill of Material Totals

You select the fields that are lower thant the level of the current row like this:

Record.SelectFields([PivotedFields], List.Select(Record.FieldNames([PivotedFields]), (x)=> Number.From(x)<=[#"Level - Copy"]))

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkOlWJ1oJSMgywmITcE8YyDLGYiNwTwTIMsFiI3gKl3h+kAq3VB4LhBTYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Level = _t, Part = _t, qty = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}, {"Part", type text}, {"qty", Int64.Type}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Group", 0}}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Replaced Errors",{{"Index", Order.Ascending}})),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"Group"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index", "Index.1", "Previous.Level"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Level", "Level - Copy"),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Duplicated Column", {{"Level", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Duplicated Column", {{"Level", type text}}, "en-GB")[Level]), "Level", "qty", List.Sum),
#"Filled Down1" = Table.FillDown(#"Pivoted Column", Table.ColumnNames(#"Pivoted Column")),
SelectAllPivotedFields = Table.AddColumn(#"Filled Down1", "PivotedFields", each Record.SelectFields(_, List.Difference(Record.FieldNames(_), {"Part", "Group", "Level - Copy"}))),
SelectPivotFieldsWithLowerLevel = Table.AddColumn(SelectAllPivotedFields, "SelectedPivotFields", each Record.SelectFields([PivotedFields], List.Select(Record.FieldNames([PivotedFields]), (x)=> Number.From(x)<=[#"Level - Copy"]))),
MultiplyAllElements = Table.AddColumn(SelectPivotFieldsWithLowerLevel, "RolledUpQty", each List.Product(Record.FieldValues([SelectedPivotFields]))),
#"Grouped Rows" = Table.Group(MultiplyAllElements, {"Part"}, {{"TotalQty", each List.Sum([RolledUpQty]), type number}})
in
#"Grouped Rows"

Proud to be a Datanaut!

Imke Feldmann

Regular Visitor

## Re: Bill of Material Totals

Totally awesome and much more elegant. thank you

Regular Visitor

## Re: Bill of Material Totals

Hi Imke

Part D is not totaling correctly with the code that you supplied. Can you please re check it?

LevelPartqty

 1 A 1 2 B 5 3 C 3 4 D 10 2 E 2 3 F 1 3 D 5
Regular Visitor

## Re: Bill of Material Totals

Hi Imke

Part D is not totaling correctly with the code that you supplied. Can you please recheck it?

LevelPartqty

 1 A 1 2 B 5 3 C 3 4 D 10 2 E 2 3 F 1 3 D 5