cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hk2018086 Regular Visitor
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
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).

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




hk2018086 Regular Visitor
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.

please see the attached file.

Multilevel totals

 

 

 

hk2018086 Regular Visitor
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
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?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




hk2018086 Regular Visitor
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
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"]))

 

Paste this code into the advanced editor and follow the steps:

 

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}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Added Index1",JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Level"}, {"Previous.Level"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Group", each if [Previous.Level] >= [Level] then [Index] else null),
    #"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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




hk2018086 Regular Visitor
Regular Visitor

Re: Bill of Material Totals

Totally awesome and much more elegant. thank you Smiley Happy

hk2018086 Regular Visitor
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

1A1
2B5
3C3
4D10
2E2
3F1
3D5
hk2018086 Regular Visitor
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

1A1
2B5
3C3
4D10
2E2
3F1
3D5