Custom Column Creation help

[Anonymous]

Hi,

Can any one please help me on below issue. I have an Input table like below.

Input Table:

WONum	Assoc. Work Order number
7609450	7605060
7605060	7167140

Logic:

L1	if WONum is not repeated in Assoc. Work Order Number then its L1 (7609450)
L2	check L1 (7609450) against WO and find associat WO Number(7605060)
L3	check L2 (7605060) against WO and find associat WO Number(7167140)

Output Table:

L1	L2	L3
7609450	7605060	7167140

I have created below custom fields but L3 is not working

L1	[A=#"Renamed Columns1"[WONum], B=#"Renamed Columns1"[Assoc. Work Order Number], to = if not List.Contains(B,[WONum]) then [WONum] else null][to]
L2	[A=#"Level1"[WONum], B=#"Level1"[L1], C=#"Level1"[Assoc. Work Order Number], to = if List.Contains(B,[WONum]) then [Assoc. Work Order Number] else null][to]
L3	[A=#"Level2"[WONum], B=#"Level2"[L2], C=#"Level2"[Assoc. Work Order Number], to = if List.Contains(B,[WONum]) then [Assoc. Work Order Number] else null][to]

But getting output like below L3 is coming in seperate row instead of first row.

@Ahmedx 

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file if it suits your requirement.

New Table = 
FILTER (
    SELECTCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE ( 'Table', 'Table'[WONum], 'Table'[Assoc. Work Order number] ),
            "@L3",
                MAXX (
                    FILTER (
                        'Table',
                        'Table'[WONum] = EARLIER ( 'Table'[Assoc. Work Order number] )
                    ),
                    'Table'[Assoc. Work Order number]
                )
        ),
        "L1", 'Table'[WONum],
        "L2", 'Table'[Assoc. Work Order number],
        "L3", [@L3]
    ),
    [L3] <> BLANK ()
)

[Anonymous]

Hi @Jihwan_Kim Thanks alot for your help. Please can you help me how to add L4 also in the above example. For example WO = 7167140 is repated in AWO so how to show L4= 7123456

It's working fine upto L3 level. But in my requirement untill L3 or L4 is not repeated in WONum need to create hierarchy. 

[Jihwan_Kim · Accepted solution]

Hi,

I am a bit confused, but please check the below picture and the attached pbix file whether it suits your requirement.

[Anonymous]

Hi @Jihwan_Kim 

Sorry the above one is not working. Please can you see the below Input and expected output. Currently the above two expressions working as individual but need L3 and L4 solution in one expression. In some cases have L3 is max in some cases have L4 is max.

Logic is 

L1 = if WO is not in associate wo
L2 = check L1 against WO and find associat WO 
L3 = check L2 against WO and find assocuate WO
L4= do for L3 again

loop will continue until it doesn't find next value