Extract Multiple Product Hierarchy Levels from an SAP Product Hierarchy Key

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

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

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

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