topic Re: Create new columns with first and last header retrieved from component and header column in DAX Commands and Tips

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

Nijlal01 — Tue, 03 Jan 2023 15:23:57 GMT

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

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

Anonymous — Tue, 03 Jan 2023 15:15:42 GMT

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

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

Nijlal01 — Tue, 03 Jan 2023 15:24:34 GMT

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

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

Anonymous — Tue, 03 Jan 2023 16:09:20 GMT

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?

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

tamerj1 — Wed, 04 Jan 2023 12:50:44 GMT

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

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

Nijlal01 — Tue, 03 Jan 2023 16:14:18 GMT

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.

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

Anonymous — Wed, 04 Jan 2023 12:46:55 GMT

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

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"

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

Nijlal01 — Wed, 04 Jan 2023 15:15:59 GMT

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

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

Anonymous — Wed, 04 Jan 2023 15:26:01 GMT

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

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

Nijlal01 — Wed, 04 Jan 2023 15:39:57 GMT

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.

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

Anonymous — Wed, 04 Jan 2023 21:09:42 GMT

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

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

Anonymous — Thu, 05 Jan 2023 08:23:57 GMT

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

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

Nijlal01 — Fri, 06 Jan 2023 13:04:23 GMT

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

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

tamerj1 — Fri, 06 Jan 2023 14:55:08 GMT

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.