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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 378 members 4,366 guests
Please welcome our newest community members: