Bill of material hierarchy

[PekkaKytosaho]

Hi,

pls help. I am stuck with this.

In the BOM data, hierarchy levels are coded in 'Level'-column as follows:

Need to get price summaries over assemblies and levels by their descriptions.

Appreciate your help.

[PekkaKytosaho]

Please find the data sample Excel here.

Data has 9 hierarchy levels.

[v-frfei-msft]

Hi @PekkaKytosaho ,

Sorry I cannot get you by the data you shared. Could you please tell me what is your excepted result then?

Regards,

Frank

[PekkaKytosaho]

Thank you for your quick response. Sorry for not clarifying enough.

Now there is bigger data sample.

Aiming for Hierarcy slicer filter. Selecting hierarchies by nested 'Description'-values. 

As result getting Power BI report table with list of child parts, quantities, prices and total sum of selected.

First thought was that several columns must be created from original 'Level' column, from level 1 to level 9...

[v-frfei-msft]

Hi @PekkaKytosaho ,

One sample for your reference.

1. We can split the Level column in Power query by "." Please refere to the M code as below.

let
    Source = Excel.Workbook(File.Contents("D:\Case\20190222\BOM v2.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Level", type text}, {"Assembly or Part", type text}, {"Code", Int64.Type}, {"Description", type text}, {"Qty", Int64.Type}, {"Unit price", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Level", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Level.1", "Level.2", "Level.3", "Level.4", "Level.5", "Level.6", "Level.7", "Level.8", "Level.9"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Level.1", Int64.Type}, {"Level.2", Int64.Type}, {"Level.3", Int64.Type}, {"Level.4", Int64.Type}, {"Level.5", Int64.Type}, {"Level.6", Int64.Type}, {"Level.7", Int64.Type}, {"Level.8", Int64.Type}, {"Level.9", Int64.Type}})
in
    #"Changed Type1"

2. Get the Hierachy slicer from marketplace. And create a measure as below.

Measure = SUM(Sheet1[Qty])*SUM(Sheet1[Unit price])

Please find the pbix as attached.

Regards,

Frank

[PekkaKytosaho]

Thanks. Helped one step further.

Need to see descriptions in HierarchySlicer as follows

Hierarchy slicer with Descriptions neededAssembly "Name811" (Level 4) would bring all its children to report table

btw, when opening pbix -'Unable to open document. The queries were authored with newer version of Power BI....' - opened the file anyway with 2019 Feb version.