Creating a Table from Filtering two tables Data and Mapping

[NMahi1703]

Hello , 

I need to create a table with by Mapping of Two tables.
Table1:

Account ID	ID
3630	1811
7625	3291
7625	5567
3631	5567
3631	3201

Table 2: 

Account Number	BilliD	Product Name	Version		Acc Status
3630	NA	P1	1		Warning
3630	NA	P2	2		Normal
3630	NA	P3	2		Normal
3630	NA	P4	10		Normal
7625	3291	P5	2		Warning
7625	3291	P6	9		Warning
8888	3291	P7	4		Critical
7625	4691	P8	2		Warning
7625		P9	6		Normal
7625		P10	7		Critical
7625		P11	8		Normal
3631	5567	P12	7		Normal
3631	5567	P13	10		Normal
3631		P14	5		Critical

Table 1 of Account Id is Having RelationShip with Table 2 of Account Number.

Conditions to Filter Data:

1. Table1 ID is matched with Table2 BilliD then Account Number Product should map with BillId Name
2. If Table1 ID is not matched or found in Table3 BilliD then Name Rows Assoisated with Account Number Name's Should Display in Table.
3. Table1 ID may be Assosiated with Different Account ID's we need to filter Based on Account ID.
Output: 

ID	Name	Status	Account ID	Acc Status
1811	P1	Warning	3630	Warning
1811	P2	Warning	3630	Normal
1811	P3	Warning	3630	Normal
1811	P4	Warning	3630	Normal
3291	P5	Warning	7625	Warning
3291	P6	Warning	7625	Warning
5567	P5	Critical	7625	Warning
5567	P6	Critical	7625	Warning
5567	P8	Critical	7625	Warning
5567	P9	Critical	7625	Normal
5567	P10	Critical	7625	Critical
5567	P11	Critical	7625	Normal
5567	p12	Normal	3631	Normal
5567	p13	Normal	3631	Normal
3201	p12	Normal	3631	Critical
3201	p13	Normal	3631	Critical
3201	p14	Critical	3631	Critical

Kindly help here.

Thanks a lot.

[lbendlin]

Your tables have a many to many relationship which is not ideal.  But for the purpose of your question it will work.

For your "Status" column you need to do some more work as Power BI has no idea what Normal/Warning/Critical means and which one is more important. You can add a reference table for that or add a numeric value into Table 2.

See attached.

[NMahi1703]

Hi @lbendlin ,

Case 1:

Table1
ID	Account ID
1811	3630

Table2 

Account Number	BilliD	Product Name	Version	Acc Status
3630	NA	P1	1	Warning
3630	NA	P2	2	Normal
3630	NA	P3	2	Normal
3630	NA	P4	10	Normal

So it should display All the Products as Billing GSID value is not matching with Table 1 of Account Number.

Acc Status may have Critical, Warning, Normal as status in above for 3630 we have Warning and Normal Status now we have to display Warning as status which is high value this value should be displaying Warning in New Status Column.
Output

ID	Name	Status	Account ID	Acc Status
1811	P1	Warning	3630	Warning
1811	P2	Warning	3630	Normal
1811	P3	Warning	3630	Normal
1811	P4	Warning	3630	Normal

Case2 :

Table1
ID	Account ID
3291	7625

Table2 

Account Number	BilliD	Product Name	Version	Acc Status
7625	3291	P5	2	Warning
7625	3291	P6	9	Warning
7625	4691	P8	2	Warning
7625		P9	6	Normal
7625		P10	7	Critical
7625		P11	8	Normal

in the Above scenario ID value is 3291 is matching with BilliD 3291 which has two records now output will be

Output:

ID	Name	Status	Account ID	Acc Status
3291	P5	Warning	7625	Warning
3291	P6	Warning	7625	Warning

Case 3:

Table1
ID	Account ID
5567	7625

table2

Account Number	BilliD	Product Name	Version	Acc Status
7625	3291	P5	2	Warning
7625	3291	P6	9	Warning
7625	4691	P8	2	Warning
7625		P9	6	Normal
7625		P10	7	Critical
7625		P11	8	Normal

In the above case there is no BilliD is matching with your account hence needs to display all the products.

Output:

ID	Name	Status	Account ID	Acc Status
5567	P5	Critical	7625	Warning
5567	P6	Critical	7625	Warning
5567	P8	Critical	7625	Warning
5567	P9	Critical	7625	Normal
5567	P10	Critical	7625	Critical
5567	P11	Critical	7625	Normal

Case 4:

Table1
Account ID	ID
3631	5567

Account Number	BilliD	Product Name	Version	Acc Status
3631	5567	P12	7	Normal
3631	5567	P13	10	Normal
3631		P14	5	Critical

In the above case ID 5567 Is matching with BilliD 5567 hence that values should display

Output:

ID	Name	Status	Account ID	Acc Status
5567	p12	Normal	3631	Normal
5567	p13	Normal	3631	Normal

Case 5:

Table1
ID	Account ID
3201	3631

3631	5567	P12	7	Normal
3631	5567	P13	10	Normal
3631		P14	5	Critical

In the above 3201 is not matching with any bill id hence all the products should display

Output:

ID	Name	Status	Account ID	Acc Status
3201	p12	Critical	3631	Normal
3201	p13	Critical	3631	Normal
3201	p14	Critical	3631	Critical

Overall output with all above cases is

ID	Name	Status	Account ID	Acc Status
1811	P1	Warning	3630	Warning
1811	P2	Warning	3630	Normal
1811	P3	Warning	3630	Normal
1811	P4	Warning	3630	Normal
3291	P5	Warning	7625	Warning
3291	P6	Warning	7625	Warning
5567	P5	Critical	7625	Warning
5567	P6	Critical	7625	Warning
5567	P8	Critical	7625	Warning
5567	P9	Critical	7625	Normal
5567	P10	Critical	7625	Critical
5567	P11	Critical	7625	Normal
5567	p12	Normal	3631	Normal
5567	p13	Normal	3631	Normal
3201	p12	Critical	3631	Normal
3201	p13	Critical	3631	Normal
3201	p14	Critical	3631	Critical