empty row required if scorecard ddint availble in source data.

[Anonymous]

Hi Team,

I have EmployeeScore 1 to 5 if Employeescore didnt availble in the table i need one row emty

below my data

Employee Score	Name	Company	Sal	Destination
3	A	AA	23	IT
3	B	AA	45	SOFT
2	C	AA	45	TE
4	D	AA	77	RS
4	F	AA	88	IT

My outputshould  below table

Note:My Employeescorecard is 1 to 5 if didnt avaible its add empty row of this table

1 and 5 didnt have employee score its should be add each row like below output

Employee Score	Name	Company	Sal	Destination
3	A	AA	23	IT
3	B	AA	45	SOFT
2	C	AA	45	TE
4	D	AA	77	RS
4	F	AA	88	IT
1
2

based my daily  have all EmployeeScore (or)   one or morethan 1 missing in sourcedata.

empty row should be dynamic based source data

Thanks

Shanvitha

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

FullOuterJoin =
SUMMARIZE (
    UNION (
        VAR ALeftOuterJoinB =
            NATURALLEFTOUTERJOIN ( 'Table', RELATEDTABLE ( 'Employee Score' ) )
        RETURN
            SELECTCOLUMNS (
                ALeftOuterJoinB,
                "Employee Score", 'Table'[Employee Score],
                "Name", 'Table'[Name],
                "Company", 'Table'[Company],
                "Sal", 'Table'[Sal],
                "Destination", 'Table'[Destination],
                "Employee Score_", 'Employee Score'[Value]
            ),
        VAR AUniqueDateLevel =
            DISTINCT ( 'Table'[Employee Score] )
        RETURN
            SELECTCOLUMNS (
                CALCULATETABLE (
                    'Employee Score',
                    NOT ( 'Employee Score'[Value] IN AUniqueDateLevel )
                ),
                "Employee Score_", 'Employee Score'[Value],
                "Name", "",
                "Company", "",
                "Sal", "",
                "Destination", "",
                "Employee Score_2", 'Employee Score'[Value]
            )
    ),
    [Employee Score],
    [Name],
    [Company],
    [Sal],
    [Destination]
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.