For each date return all names even when no record in data, PowerQuery

[nahaku]

I would like to make template where i import data and it will creates report, They want from me to show there days when person did not work with value as "Not-working-day"

But I do not have this in attendance output datas. Is it possible to generate rows in merged tables in this way:

for each day return all employees from list even if they had no data, then i would use simple if statement: if there is no value in worked time return "Not-working-day"

员工工号 staff no	是否职能员工 is functional staff: Y fro yes , N for not	日期 date :yyyy-mm-dd	考勤时长 attendance hour：小时
1001	Y	2021-03-01​	9
1001	Y	2021-03-02​	9
1001	Y	2021-03-03​	10
1001	Y	2021-03-04​	7.5
1001	Y	2021-03-05​	7
1001	Y	2021-03-06​	Non-working day
1001	Y	2021-03-07​	Non-working day

 

[mart37]

When you have a table with dates you can use Power Query to combine the tables with Combine-Querie's combine

 

[nahaku]

When you have a table with dates you can use Power Query to combine the tables with Combine-Querie's combine

can you show what you mean? because i think you mean Merge, what i done already, but it only adds Names to dates where is some record, otherwise it will be just Date without any records.

 

[alansidman]

How about showing us your source data from both tables and letting us try to solve and present what you have shown (I believe) is the end result. BTW. Please use XL2BB so that we don't have to try and retype your data to solve your issue.

 

[mart37]

You can choose for join LeftOuter or FullOuter.
= Table.NestedJoin(#"Typ changed", {"Date"}, tbl_date, {"dateworking"}, "tbl_hours", JoinKind.LeftOuter)

 

[nahaku]

How about showing us your source data from both tables and letting us try to solve and present what you have shown (I believe) is the end result. BTW. Please use XL2BB so that we don't have to try and retype your data to solve your issue.

its quite simple, I have 2 tables: Date table contains only dates second table NameID contains: Name and ID
So I need to join this 2 tables without any common column so each date will have all IDs , then i can use merge this new table to add attendance records.

Template.xlsx
	I	J	K	L	M	N	O	P
4	Date Table		NameID Table			Combined Table:
5	Date		name	ID		Date	name	ID
6	2021-03-01		Bob	111		2021-03-01	Bob	111
7	2021-03-02		Sam	222		2021-03-01	Sam	222
8	2021-03-03		Ivan	333		2021-03-01	Ivan	333
9	2021-03-04		Peter	444		2021-03-01	Peter	444
10						2021-03-02	Bob	111
11						2021-03-02	Sam	222
12						2021-03-02	Ivan	333
13						2021-03-02	Peter	444
14						2021-03-03	Bob	111
15						2021-03-03	Sam	222
16						2021-03-03	Ivan	333
17						2021-03-03	Peter	444
18						2021-03-04	Bob	111
19						2021-03-04	Sam	222
20						2021-03-04	Ivan	333
21						2021-03-04	Peter	444
HelpSheet

 

[alansidman]

A cartesian Join in PQ

 

[nahaku]

A cartesian Join in PQ

Thank you, this is what i was looking for.