Create new columns with first and last header retrieved from component and header column

[Nijlal01]

Hi all,

I have data of the column Component and Header available and want to create to extra columns which return the start and end header like in the example below. Anyone who knows how to do so? (either in DAX (measure or calculated column) or PowerQuery).

So you go from 1-->2-->3-->4 which means the start header is 2 and end header is 4

So you go from 5-->6-->7-->8 which means the start header is 6 and end header is 8

Component	Header	StartHeader	EndHeader
1	2	2	4
2	3	2	4
3	4	2	4
5	6	6	8
6	7	6	8
7	8	6	8

KR,

Lars

[Anonymous · Accepted solution]

Here's my approach usin PowerQuery only. The Final table - Query 6 should give you the result.

Query1: ComponentHeader Source

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJSitWJBpI6SsZgljGQZQJmmQJZZmCWGZBlDmaZA1kWSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Component = _t, Header = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Component", type text}, {"Header", type text}})
in
#"Changed Type"

Query2: ComponentHeader Source - Headers

let
Source = #"ComponentHeader Source",
#"Removed Columns" = Table.RemoveColumns(Source,{"Component"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Header", "Component"}})
in
#"Renamed Columns"

Query3:  ComponentHeader Source - Components

let
Source = #"ComponentHeader Source",
#"Removed Columns" = Table.RemoveColumns(Source,{"Header"})
in
#"Removed Columns"

Query4: Components HighestParent

let
Source = Table.Combine({#"ComponentHeader Source - Components", #"ComponentHeader Source - Headers"}),
#"Removed Duplicates" = Table.Distinct(Source),
#"Merged Queries" = Table.NestedJoin(#"Removed Duplicates", {"Component"}, #"ComponentHeader Source", {"Component"}, "ComponentHeaderHierachy", JoinKind.LeftOuter),
#"Expanded ComponentHeaderHierachy - Ref" = Table.ExpandTableColumn(#"Merged Queries", "ComponentHeaderHierachy", {"Header"}, {"Header"}),
#"Inserted Merged Column" = Table.AddColumn(#"Expanded ComponentHeaderHierachy - Ref", "CHKey", each Text.Combine({"C", Text.From([Component], "da-DK"), "H", Text.From([Header], "da-DK")}), type text),

ListChild = List.Buffer( #"Inserted Merged Column"[Component] ),
ListParent = List.Buffer( #"Inserted Merged Column"[Header] ),

fnGetHighestParent = (n as text) as text =>
let
PosOfParent = List.PositionOf( ListChild, n ),
ParID = ListParent{PosOfParent}
in
if ParID = null then ListChild{PosOfParent} else @fnGetHighestParent(ListParent{PosOfParent}),

HighestParent = Table.AddColumn( #"Inserted Merged Column",
"HighestParent",
each fnGetHighestParent( [Component] ),
type text)
in
HighestParent

Query5: Components LowestParent

let
Source = Table.Combine({#"ComponentHeader Source - Components", #"ComponentHeader Source - Headers"}),
#"Removed Duplicates1" = Table.Distinct(Source),
#"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates1",{{"Component", "Header"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Header"}, #"ComponentHeader Source", {"Header"}, "ComponentHeaderHierachy", JoinKind.LeftOuter),
#"Expanded ComponentHeaderHierachy" = Table.ExpandTableColumn(#"Merged Queries", "ComponentHeaderHierachy", {"Component"}, {"ComponentHeaderHierachy.Component"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded ComponentHeaderHierachy",{{"ComponentHeaderHierachy.Component", "Component"}}),
#"Inserted Merged Column" = Table.AddColumn(#"Renamed Columns1", "CHKey", each Text.Combine({"C", Text.From([Component], "da-DK"), "H", Text.From([Header], "da-DK")}), type text),

ListChild = List.Buffer( #"Inserted Merged Column"[Header] ),
ListParent = List.Buffer( #"Inserted Merged Column"[Component] ),

fnGetHighestParent = (n as text) as text =>
let
PosOfParent = List.PositionOf( ListChild, n ),
ParID = ListParent{PosOfParent}
in
if ParID = null then ListChild{PosOfParent} else @fnGetHighestParent(ListParent{PosOfParent}) & "|" & n ,

LowestParent = Table.AddColumn( #"Inserted Merged Column",
"LowestParent",
each Text.Middle(fnGetHighestParent( [Header] ), 2, 1),
type text),
#"Reordered Columns" = Table.ReorderColumns(LowestParent,{"Component", "Header", "CHKey", "LowestParent"})
in
#"Reordered Columns"

Query6: ComponentHeader FinalTable

let
Source = Table.NestedJoin(#"Components LowestParent", {"CHKey"}, #"Components HighestParent", {"CHKey"}, "Components HighestParent", JoinKind.FullOuter),
#"Expanded Components Append (2)" = Table.ExpandTableColumn(Source, "Components HighestParent", {"HighestParent"}, {"HighestParent"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Components Append (2)", each ([Component] <> null))
in
#"Filtered Rows"

[Nijlal01]

Hi all,

A little bit extended example. The component and header codes are created by the system and not necessarily higher for a component then a header or the other way around

Component Code	Component Desc	Header	Header Desc	StartHeader
27869	Malt	100097	Wort	100097
100097	Wort	100047	Green Beer	100097
100047	Green Beer	100223	Mature Beer	100097
100223	Mature Beer	116809	Bright Beer Keg	100097
100223	Mature Beer	175772	Bright Beer Can	100097
100223	Mature Beer	175773	Bright Beer Bottle	100097
116809	Bright Beer Keg	175515	Keg 50L	100097
116809	Bright Beer Keg	175516	Keg 30L	100097
175773	Bright Beer Bottle	175540	Bottle 33Cl	100097
175772	Bright Beer Can	174946	Can 33 Cl	100097

[Anonymous]

The PowerQuery I posted below should work regardless 🙂 Please check.

[Nijlal01 · Accepted solution]

Wooow! First time I did copy queries like this but it worked:

Did I do it correctly?

And now I have to retrieve it from the real Bill of Material table instead of the manually created simplified one.

[Anonymous]

I think you have marked your post as the solution. I proposed my answer in another post below.

[Anonymous]

Please mark my answer as solution as it worked for you.

[Anonymous · Accepted solution]

Here's my approach usin PowerQuery only. The Final table - Query 6 should give you the result.

Query1: ComponentHeader Source

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJSitWJBpI6SsZgljGQZQJmmQJZZmCWGZBlDmaZA1kWSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Component = _t, Header = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Component", type text}, {"Header", type text}})
in
#"Changed Type"

Query2: ComponentHeader Source - Headers

let
Source = #"ComponentHeader Source",
#"Removed Columns" = Table.RemoveColumns(Source,{"Component"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Header", "Component"}})
in
#"Renamed Columns"

Query3:  ComponentHeader Source - Components

let
Source = #"ComponentHeader Source",
#"Removed Columns" = Table.RemoveColumns(Source,{"Header"})
in
#"Removed Columns"

Query4: Components HighestParent

let
Source = Table.Combine({#"ComponentHeader Source - Components", #"ComponentHeader Source - Headers"}),
#"Removed Duplicates" = Table.Distinct(Source),
#"Merged Queries" = Table.NestedJoin(#"Removed Duplicates", {"Component"}, #"ComponentHeader Source", {"Component"}, "ComponentHeaderHierachy", JoinKind.LeftOuter),
#"Expanded ComponentHeaderHierachy - Ref" = Table.ExpandTableColumn(#"Merged Queries", "ComponentHeaderHierachy", {"Header"}, {"Header"}),
#"Inserted Merged Column" = Table.AddColumn(#"Expanded ComponentHeaderHierachy - Ref", "CHKey", each Text.Combine({"C", Text.From([Component], "da-DK"), "H", Text.From([Header], "da-DK")}), type text),

ListChild = List.Buffer( #"Inserted Merged Column"[Component] ),
ListParent = List.Buffer( #"Inserted Merged Column"[Header] ),

fnGetHighestParent = (n as text) as text =>
let
PosOfParent = List.PositionOf( ListChild, n ),
ParID = ListParent{PosOfParent}
in
if ParID = null then ListChild{PosOfParent} else @fnGetHighestParent(ListParent{PosOfParent}),

HighestParent = Table.AddColumn( #"Inserted Merged Column",
"HighestParent",
each fnGetHighestParent( [Component] ),
type text)
in
HighestParent

Query5: Components LowestParent

let
Source = Table.Combine({#"ComponentHeader Source - Components", #"ComponentHeader Source - Headers"}),
#"Removed Duplicates1" = Table.Distinct(Source),
#"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates1",{{"Component", "Header"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Header"}, #"ComponentHeader Source", {"Header"}, "ComponentHeaderHierachy", JoinKind.LeftOuter),
#"Expanded ComponentHeaderHierachy" = Table.ExpandTableColumn(#"Merged Queries", "ComponentHeaderHierachy", {"Component"}, {"ComponentHeaderHierachy.Component"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded ComponentHeaderHierachy",{{"ComponentHeaderHierachy.Component", "Component"}}),
#"Inserted Merged Column" = Table.AddColumn(#"Renamed Columns1", "CHKey", each Text.Combine({"C", Text.From([Component], "da-DK"), "H", Text.From([Header], "da-DK")}), type text),

ListChild = List.Buffer( #"Inserted Merged Column"[Header] ),
ListParent = List.Buffer( #"Inserted Merged Column"[Component] ),

fnGetHighestParent = (n as text) as text =>
let
PosOfParent = List.PositionOf( ListChild, n ),
ParID = ListParent{PosOfParent}
in
if ParID = null then ListChild{PosOfParent} else @fnGetHighestParent(ListParent{PosOfParent}) & "|" & n ,

LowestParent = Table.AddColumn( #"Inserted Merged Column",
"LowestParent",
each Text.Middle(fnGetHighestParent( [Header] ), 2, 1),
type text),
#"Reordered Columns" = Table.ReorderColumns(LowestParent,{"Component", "Header", "CHKey", "LowestParent"})
in
#"Reordered Columns"

Query6: ComponentHeader FinalTable

let
Source = Table.NestedJoin(#"Components LowestParent", {"CHKey"}, #"Components HighestParent", {"CHKey"}, "Components HighestParent", JoinKind.FullOuter),
#"Expanded Components Append (2)" = Table.ExpandTableColumn(Source, "Components HighestParent", {"HighestParent"}, {"HighestParent"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Components Append (2)", each ([Component] <> null))
in
#"Filtered Rows"

[Nijlal01]

Hi,

Unfortunatly I do not get it to work with the real data. Partially because the length of component and header are not always the same, but I think also more is going on. I added the real data in the link: https://1drv.ms/u/s!AtVSbJ_QI8gshbEp63CfW_kjNTz4QQ?e=tCHY2C .

Could you help me out in finding out what is going wrong?

KR,

Lars

[tamerj1]

Hi @Nijlal01 
Please provide little more clarification. At least please provide the expected results for the sample data in the excel sheet perhaps we can understand the patern that you're looking for.

[tamerj1]

Hi @Nijlal01 

Please create new calculated column

StartHeader =
MINX (
    FILTER (
        'Table',
        QUOTIENT ( 'Table'[Component], 4 )
            = QUOTIENT ( EARLIER ( 'Table'[Component] ), 4 )
    ),
    'Table'[Header]
)

Replace MINX with MAXX for EndHeader

Also you can create a calculated column 

Rank =
QUOTIENT ( 'Table'[Component], 4 )

then you can do

StartHeader =
CALCULATE ( MIN ( 'Table'[Component] ), ALLEXCEPT ( 'Table', 'Table'[Rank] ) )

[Anonymous]

@Nijlal01 , so you want to calculate the values for [Start Header] and [End Header] from the values in [Component] and [Header], is that right?

In the sample data given, for the three rows with [Component] 1, 2, and 3, you have [End Header] of 4. What logic do you use to get an [End Header] value of 4?

[Nijlal01]

Thats right.

The logic to determine is done by my head for this example. I could not think of an easy technical way in dax/powerquery/excel thats why I posted this challenge ;). The reasoning why I want this is to be able to filter on a start/end header and see all steps after/before.

[Anonymous]

Just to clraify , for the second hierarchy, the start header should be 6. Isn't it?

[Nijlal01]

Sorry, for the second hierarchy it should indeed be 6. I edited it in the original post.