Extract Multiple Product Hierarchy Levels from an SAP Product Hierarchy Key

SAP Product Hierarchy 0101010101 maps to Level 1 **01**, Level 2 **0101**, Level 3 **010101** Level 4 **01010101** and Level 5 **01010101**

me@jaykilleen.com wrote this about 1 year ago and it was last updated about 1 year ago.


← Back to the Posts

Given a 5 level SAP Product Hierarchy table that is categorised like below:

level category
1 Vehicles
2 Models
3 Types
4 Variants
5 Colours

And then given an Product Hierarchy table (a combination of T179 and T179T) with data mapped to those categories like:

id name level
01 Car 1
0101 Mark4 2
010101 Sedan 3
01010101 Standard 4
0101010101 Black 5
01 Car 1
0101 Mark4 2
010101 Sedan 3
01010101 Standard 4
0101010102 Red 5
01 Car 1
0101 Mark4 2
010102 HatchBack 3
01010201 Standard 4
0101020101 Black 5
01 Car 1
0102 Mark4 2
010201 HatchBack 3
01020102 Advanced Safety Pack 4
0102010201 Black 5

And then a table of Materials (MARA) that belongs to that Product Hierarchy table like:

id name product_hierarchy_id
10000001 Mark4 Black Sedan 0101010101
10000002 Mark4 Red Sedan 0101010102
10000002 Mark4 Black Hatchback 0101020101
10000002 Mark4 Black Hatchback Safety Editition 0102010201

You may want, on the product_hierarchies table, extra columns that break apart the product_hierarchy_id / id into 5 columns with a value that represents each level of the product hierarchy.

This can then be merged on the materials table to do things like, selecting all materials that are within product hierarchy level 3 010101.

Using Power Query M Formula Language this can be done using:

let
    Source = product_hierarchies,
    #"Added PH1" = Table.AddColumn(Source, "ph1", each if Text.Length([id]) > 0 then Text.Range([id],0,2) else "", type text),
    #"Added PH2" = Table.AddColumn(#"Added PH1", "ph2", each if Text.Length([id]) > 2 then Text.Range([id],0,4) else "", type text),
    #"Added PH3" = Table.AddColumn(#"Added PH2", "ph3", each if Text.Length([id]) > 4 then Text.Range([id],0,6) else "", type text),
    #"Added PH4" = Table.AddColumn(#"Added PH3", "ph4", each if Text.Length([id]) > 6 then Text.Range([id],0,8) else "", type text),
    #"Added PH5" = Table.AddColumn(#"Added PH4", "ph5", each if Text.Length([id]) > 8 then Text.Range([id],0,10) else "", type text)
in
    #"Added PH5"

Using SQL (in this case I am querying a MySQL database) this can be done using:

SELECT
  SUBSTRING(product_hierarchy_id, 1, 2) AS ph1_id,
  SUBSTRING(product_hierarchy_id, 3, 2) AS ph2_id,
  SUBSTRING(product_hierarchy_id, 5, 2) AS ph3_id,
  SUBSTRING(product_hierarchy_id, 7, 2) AS ph4_id,
  SUBSTRING(product_hierarchy_id, 9, 2) AS ph5_id,
FROM
  product_hierarchies

Similar