Ignore null values in dimensions

[TinaH]

Hello together,

I am currently having a problem using multiple dimensions with many null vlaues.  Suppose i have the following dimensions / values:

Role	2022-07	2022-08
2nd Level Support	Employee 1	-
2nd Level Support	Employee 2	-
2nd Level Support	-	Employee 3
2nd Level Support	-	Employee 4
Systemupdate - Code	Employee 5	-
Systemupdate - Code	-	Employee 6
Systemupdate - SQL	Employee 7	-
Systemupdate - SQL	-	Employee 8

But my goal is that the table looks as follows:

Role	2022-07	2022-08
2nd Level Support	Employee 1	Employee 3
2nd Level Support	Employee 2	Employee 4
Systemupdate - Code	Employee 5	Employee 6
Systemupdate - SQL	Employee 7	Employee 8

How can i achieve this?

Thank you!!

Best regards
Tina

[chris_djih]

For me it looks like your challenge is not ignoring null values but get a better matching of the data model.

Can you provide more details on  your example tables: is this your raw data or is this already a result of scriptung?
What is the goal of this table? Is this a kind of employee schedule?

[TinaH]

Yes, the objective of the app should be an employee schedule.

The basis for this is an Excel table. At the top you can find the respective calendar weeks as numbers and then the assignment of the employees to the roles per calendar week. Currently, I read the Excel file twice in succession, always for the respective week, i.e.:

let vWeek = '['&week(today())&']';
let vYear = '['&year(today())&']';

[MITARBEITER]:
LOAD 
    [F2]		AS Emplyee,
    '1'			AS Kontext,
    Pick
    (
        match
        ( 
        	$(vWeek)
            ,	'a'
            ,	'f'
            ,	'c'
            ,	's'
            ,	'r'
        )
        
        ,	'abwesend'
        ,	'2nd Level Support'
        ,	'Systemupdate - Code'
        ,	'Systemupdate - SQL'
        , 	'Release Manager'
	) AS Role

FROM [lib://AttachedFiles/Schedule.xlsx]
(ooxml, embedded labels, table is $(vYear))

And the same code for the following week, but '2' AS Kontext.

My objective is to display the table as described in my question above. Does this help?

[chris_djih]

"And the same code for the following week" - and here lays the interesting point.
I assume you load it exactly the same wiht out any join or something like this?
Then it will automatically concatenate (append the table):
If you want to combine the lines, i think you need to join them.

[TinaH]

Yes exactly: I load the data for the following week the same way, without using any join. 

However, when I join the tables, I get the same result as above. Would it help if I publish an example file?

[chris_djih]

Yes, that would be very helpful. please give an example of the rawdata as well. so the community can puzzle around to get to the correct result.

[TinaH]

Please find attached the example file as well as an example of raw data.

Thank you very much!

[TinaH]

Someone any ideas?