Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PekkaKytosaho
Frequent Visitor

Bill of material hierarchy

Hi,

pls help. I am stuck with this.


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

EPDM.JPG

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

 

Appreciate your help.

 

 

 

5 REPLIES 5
PekkaKytosaho
Frequent Visitor

Please find the data sample Excel here.

Data has 9 hierarchy levels.

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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...

 

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])

 

 

Capture.PNG

 

Please find the pbix as attached.

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Thanks. Helped one step further.

 

Need to see descriptions in HierarchySlicer as follows

Hierarchy slicer with Descriptions neededHierarchy slicer with Descriptions neededAssembly "Name811" (Level 4) would bring all its children to report tableAssembly "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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.