Creating a Table from Filtering two tables Data and Mapping

Question

Hello ,&nbsp;I need to create a table with by Mapping of Two tables.Table1:Account IDID3630181176253291762555673631556736313201Table 2:&nbsp;Account NumberBilliDProduct NameVersion&nbsp;Acc Status3630NAP11&nbsp;Warning3630NAP22&nbsp;Normal3630NAP32&nbsp;Normal3630NAP410&nbsp;Normal76253291P52&nbsp;Warning76253291P69&nbsp;Warning88883291P74&nbsp;Critical76254691P82&nbsp;Warning7625&nbsp;P96&nbsp;Normal7625&nbsp;P107&nbsp;Critical7625&nbsp;P118&nbsp;Normal36315567P127&nbsp;Normal36315567P1310&nbsp;Normal3631&nbsp;P145&nbsp;CriticalTable 1 of Account Id is Having RelationShip with Table 2 of Account Number.Conditions to Filter Data:1.&nbsp;Table1 ID is matched with Table2 BilliD then Account Number Product should map with BillId Name2. 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:&nbsp;IDNameStatusAccount IDAcc Status1811P1Warning3630Warning1811P2Warning3630Normal1811P3Warning3630Normal1811P4Warning3630Normal3291P5Warning7625Warning3291P6Warning7625Warning5567P5Critical7625Warning5567P6Critical7625Warning5567P8Critical7625Warning5567P9Critical7625Normal5567P10Critical7625Critical5567P11Critical7625Normal5567p12Normal3631Normal5567p13Normal3631Normal3201p12Normal3631Critical3201p13Normal3631Critical3201p14Critical3631CriticalKindly help here.Thanks a lot.

lbendlin · Answer

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

&nbsp;
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.
&nbsp;
See attached.
&nbsp;

nmahi1703 · Answer

Hi&nbsp;lbendlin&nbsp;,Case 1:Table1&nbsp;IDAccount ID&nbsp;&nbsp;18113630Table2&nbsp;Account NumberBilliDProduct NameVersionAcc Status3630NAP11Warning3630NAP22Normal3630NAP32Normal3630NAP410NormalSo 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 IDNameStatusAccount IDAcc Status1811P1Warning3630Warning1811P2Warning3630Normal1811P3Warning3630Normal1811P4Warning3630NormalCase2 :Table1&nbsp;IDAccount ID&nbsp;&nbsp;32917625Table2&nbsp;Account NumberBilliDProduct NameVersionAcc Status76253291P52Warning76253291P69Warning76254691P82Warning7625&nbsp;P96Normal7625&nbsp;P107Critical7625&nbsp;P118Normalin the Above scenario ID value is 3291 is matching with BilliD 3291 which has two records now output will beOutput:IDNameStatusAccount IDAcc Status3291P5Warning7625Warning3291P6Warning7625WarningCase 3:Table1&nbsp;IDAccount ID&nbsp;&nbsp;55677625table2Account NumberBilliDProduct NameVersionAcc Status76253291P52Warning76253291P69Warning76254691P82Warning7625&nbsp;P96Normal7625&nbsp;P107Critical7625&nbsp;P118NormalIn the above case there is no BilliD is matching with your account hence needs to display all the products.Output:IDNameStatusAccount IDAcc Status5567P5Critical7625Warning5567P6Critical7625Warning5567P8Critical7625Warning5567P9Critical7625Normal5567P10Critical7625Critical5567P11Critical7625NormalCase 4:Table1&nbsp;Account IDID&nbsp;&nbsp;36315567&nbsp;Account NumberBilliDProduct NameVersionAcc Status36315567P127Normal36315567P1310Normal3631&nbsp;P145CriticalIn the above case ID 5567 Is matching with BilliD 5567 hence that values should displayOutput:IDNameStatusAccount IDAcc Status5567p12Normal3631Normal5567p13Normal3631NormalCase 5:Table1&nbsp;IDAccount ID&nbsp;&nbsp;32013631&nbsp;36315567P127Normal36315567P1310Normal3631&nbsp;P145CriticalIn the above 3201 is not matching with any bill id hence all the products should displayOutput:IDNameStatusAccount IDAcc Status3201p12Critical3631Normal3201p13Critical3631Normal3201p14Critical3631CriticalOverall output with all above cases isIDNameStatusAccount IDAcc Status1811P1Warning3630Warning1811P2Warning3630Normal1811P3Warning3630Normal1811P4Warning3630Normal3291P5Warning7625Warning3291P6Warning7625Warning5567P5Critical7625Warning5567P6Critical7625Warning5567P8Critical7625Warning5567P9Critical7625Normal5567P10Critical7625Critical5567P11Critical7625Normal5567p12Normal3631Normal5567p13Normal3631Normal3201p12Critical3631Normal3201p13Critical3631Normal3201p14Critical3631Critical

Forum Discussion

Creating a Table from Filtering two tables Data and Mapping

2 Replies

Related content

Creating a Date Filter in Power BI

Create a map by postcode

creating map

Creating Map layer

Map visual Filtering - Tooltip

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

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

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

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