Conditional Column from Two Different Tables

[Anonymous]

I have two tables.

Table A - A list of all locations that have ever existed and the data related to that location.
Table B - A list of all locations that have ever existed, with a column on the current status of that location. (Open, Closed, or New)

I need Table A to only show me data about sites that are currently OPEN or NEW. I dont care if the site is closed.

I have tried appending Table B to Table A but it only adds a bunch of null rows for the duration of Table A, after which it adds the Data for Table B, nulling out the columns for Table A.


I'm not sure how to do this but it seems like a relatively simple task to accomplish.
Please let me know if there is any further information I could provide.

Table A

Sites	Subnets	IPs
1002	1.1.1	1.1.1.250
1004	1.1.2	1.1.2.250
1005	1.1.3	1.1.3.250
1006	1.1.4	1.1.4.250
1007	1.1.5	1.1.5.250
1008	1.1.6	1.1.6.250
1010	1.1.7	1.1.7.250
1011	1.1.8	1.1.8.250
1014	1.1.9	1.1.9.250
1015	1.1.10	1.1.10.250
1016	1.1.11	1.1.11.250
1017	1.1.12	1.1.12.250
1018	1.1.13	1.1.13.250
1021	1.1.14	1.1.14.250
1022	1.1.15	1.1.15.250
1023	1.1.16	1.1.16.250
1025	1.1.17	1.1.17.250
1026	1.1.18	1.1.18.250
1027	1.1.19	1.1.19.250
1031	1.1.20	1.1.20.250
1032	1.1.21	1.1.21.250
1033	1.1.22	1.1.22.250
1035	1.1.23	1.1.23.250
1037	1.1.24	1.1.24.250
1038	1.1.25	1.1.25.250
1039	1.1.26	1.1.26.250
1040	1.1.27	1.1.27.250
1041	1.1.28	1.1.28.250
1043	1.1.29	1.1.29.250
1044	1.1.30	1.1.30.250
1045	1.1.31	1.1.31.250
1046	1.1.32	1.1.32.250
1047	1.1.33	1.1.33.250
1049	1.1.34	1.1.34.250
1050	1.1.35	1.1.35.250
1051	1.1.36	1.1.36.250
1054	1.1.37	1.1.37.250
1055	1.1.38	1.1.38.250

 

Table B

Restaurant	Wan_Status
1001	Closed
1002	Closed
1004	Closed
1005	Complete
1006	Closed
1007	Complete
1008	Complete
1009	Closed
1010	Closed
1011	Closed
1012	Closed
1014	Complete
1015	Complete
1016	Complete
1017	Closed
1018	Complete
1020	Closed
1021	Complete
1022	Complete
1023	Complete
1025	Complete
1026	Complete
1027	Complete
1031	Closed
1032	Complete
1033	Complete
1034	Closed
1035	Closed
1037	Complete
1038	Complete
1039	Complete
1040	Complete
1041	Complete
1043	Complete
1044	Complete
1045	Complete
1046	Complete
1047	Complete

[V-pazhen-msft]

@Anonymous 

It seems they are related by the sites and restaurant, you could create the expected table with this formula:

Table = CALCULATETABLE(VALUES('Table A'),FILTER('Table B',[Wan_Status]<>"Closed"))

Paul Zheng

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

[VasTg]

@Anonymous 

Could you provide a sample data of two tables and what is the key to join them?

Preferable an excel format instead of image..

[Anonymous]

I'm unable to upload any files to a file sharing website but i've added some sample data to my original post.