Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Custom Column Creation help

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:

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

 

Output Table:

L1L2L3
760945076050607167140

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 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

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

     

     

     

     

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Jihwan_Kim
        Super User
        3 years ago

        Hi,

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