create column dynamically from other table

[dorsey]

Hello everyone,

First sorry for my bad english.

I'm carry on this powerful software and I've a new question. 

To explain my problème, I have 2 tables

the first one is like this :

NAME	SURNAME
John	Doe
John	Maclane
Mary	Poppins

The second one have on the first colunn liste of date create by calendar function et other column will be creatte dynamically with the name of the first table like this : 

date	Doe	Maclane	poppins	etc...	etc...
24/06/2021	...	...	...	...	..

I have see we can create row dynamically but I don't know (Or don't see) if it's possible on this way.

Thank you for your help

regards

[dorsey]

Yes of course.

This my first table : 

On the second one, on all day find who is missing (something like that) :

DATE	||NAME	||SURNAME
01/01/2021	John	Rambo
01/01/2021	John	otherone
...	...	...
02/08/2021	doe	gregoire
02/08/2021	does	bérengère
02/08/2021	mclane	gregory
02/08/2021	poppins	sheryl
03/08/2021	...	...

[v-kelly-msft]

Hi  @dorsey ,

So you wanna find the missing people from your first table(you provided in your last reply) and below table?

Then get a final table something like below:

Did I understand correctly?If possible,could you pls share a sample .pbix file for test?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[dorsey]

Yes that right.

The file is available here : https://altissimalearning.net/Holiday_test.pbix

thank you

[amitchandak]

@dorsey , it is better to unpivot the second table in power query and get first name. Then you can create a common first name column

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

[dorsey]

Hey ! 

Thank you for your answer, it's working perfectly and I think it will be very usefull.

But finaly, my problème is unsolved, my fault of course because it's a logical problem.

I need to find if my user have check this day or not, and I think finaly I need to combinate two table.

the first one don't change but the seconde one need to create the row dynamicaly.

I explain : 

the first one is always  :

NAME	SURNAME
John	Doe
John	Maclane
Mary	Poppins

but the second one need reppeat the date any time there is user on the first table : 

DATE	||NAME	||SURNAME
01/01/2021	John	Doe
01/01/2021	John	Maclane
01/01/2021	Mary	Poppins
02/01/2021	John	Doe
02/01/2021	John	Maclane
03/01/2021	John	Doe
03/01/2021	...	...

[v-kelly-msft]

Hi @dorsey,

For rows 2021/2/1,why there are only 2 rows?Shouldnt there be 3 rows,which is same as 2021/1/1?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[dorsey]

Hi,

Thank you for reply.

Not really because it's a table for the holiday who user have take. For exemple the 2021/1/1, three persons have take the day but the day after only two

regards.

[v-kelly-msft]

Hi  @dorsey ,

Then how to check how many people take the day?Could you show more sample data with expected output to make the logic clear？

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!