Create new table based on values of several columns

[dnsia]

How do I create a new table that only shows the actual module names tagged as N in column In use.

Conditions:

1. If sublevel III has no value, sublevel II value should be listed

2. If sublevel II has no value, sublevel I value be listed.

3. If sublevel I has no value, module value should be listed.

Category		Module		(Module) Sublevel I		(Module) Sublevel II		(Module) Sublevel III		In Use
SETTINGS		Application Configuration		Accounts Configuration						Y
SETTINGS		Masters		Agency Master		Agency Local Charges				N
SETTINGS		Masters		Documentation Masters		MCN Configuration		MCN Validation		Y
Commercial		Contribution		Cost		Masters		Cost Type		N
Commercial				Sales Visit Plan						N
Accounting		Agency Commission Statement								N
Accounting		Refund Payment								N
Operations		Terminal Departure Receipt								N
Operations		Reports		Cont'r Weight Discrepancy						N

Desired Table :

Category		Module Name		In Use
SETTINGS		Agency Local Charges		N
Commercial		Cost Type		N
Commercial		Sales Visit Plan		N
Accounting		Agency Commission Statement		N
Accounting		Refund Payment		N
Operations		Terminal Departure Receipt		N
Operations		Container Weight Discrepancy		N

[Samarth_18 · Accepted solution]

Hi @dnsia ,

You can create a new column in your current table with below code:-

Non_blank_module =
IF (
    Module_data[(Module) Sublevel III] <> BLANK (),
    Module_data[(Module) Sublevel III],
    IF (
        Module_data[(Module) Sublevel II] <> BLANK (),
        Module_data[(Module) Sublevel II],
        IF (
            Module_data[(Module) Sublevel I] <> BLANK (),
            Module_data[(Module) Sublevel I],
            Module_data[Module]
        )
    )
)

Now you can use Category,Non_Blank_module,in user column from this table with IN-Use = N filter:-

or you can create a new table with code:-

Table 3 =
SUMMARIZE (
    FILTER ( Module_data, Module_data[In Use] = "N" ),
    Module_data[Category],
    Module_data[Non_blank_module],
    Module_data[In Use]
)

Thanks,

Samarth

[ryan_mayu]

@dnsia 

you can either transform the table in pq or use DAX to create a new table.

pls see the attachment below.

[amitchandak]

@dnsia , Unpivot the module column in power and filter on N and save and try

https://radacad.com/pivot-and-unpivot-with-power-bi