topic Merge 2 tables into 1 overview in Power Query
https://community.powerbi.com/t5/Power-Query/Merge-2-tables-into-1-overview/m-p/1508866#M46925
<P>Good evening,</P><P> </P><P>I want to merge table 1 and table 2 into 1 overview.</P><P>But there is not a normal relation between the 2 tables.</P><P> </P><P>Sources:</P><P>Table 1</P><TABLE><TBODY><TR><TD>FileID</TD><TD>Supplier</TD></TR><TR><TD>LFI223081</TD><TD>Dunga-dunga</TD></TR><TR><TD>LFI223747</TD><TD>Sonac-Gent</TD></TR><TR><TD>LFI224207</TD><TD>Trob-Dong</TD></TR><TR><TD>LFI225815</TD><TD>Moyp-Ballym</TD></TR><TR><TD>LFI225820</TD><TD>Dunga-Dunga</TD></TR></TBODY></TABLE><P> </P><P>Table 2</P><TABLE><TBODY><TR><TD>Supplier</TD><TD>active</TD><TD>FFA</TD><TD>PH</TD><TD>NPH</TD><TD>PE</TD><TD>Ni</TD></TR><TR><TD>Dunga-dunga</TD><TD>no</TD><TD>5</TD><TD>20</TD><TD>15</TD><TD>39</TD><TD>700</TD></TR><TR><TD>Sonac-Gent</TD><TD>yes</TD><TD>1</TD><TD>10</TD><TD>5</TD><TD>20</TD><TD>550</TD></TR><TR><TD>Trob-Dong</TD><TD>yes</TD><TD>3</TD><TD>50</TD><TD>40</TD><TD>1</TD><TD>20</TD></TR><TR><TD>Moyp-Ballym</TD><TD>yes</TD><TD>6</TD><TD>300</TD><TD>250</TD><TD>35</TD><TD>1000</TD></TR><TR><TD>Dunga-dunga</TD><TD>yes</TD><TD>4</TD><TD>25</TD><TD>10</TD><TD>40</TD><TD>650</TD></TR></TBODY></TABLE><P> </P><P> </P><P>New overview</P><TABLE><TBODY><TR><TD>FileID Supplier</TD><TD>FFA</TD><TD>PH</TD><TD>NPH</TD><TD>PE</TD><TD>Ni</TD></TR><TR><TD>LFI223081</TD><TD>Dunga-dunga</TD><TD>4</TD><TD>25</TD><TD>10</TD><TD>40</TD><TD>650</TD></TR><TR><TD>LFI223747</TD><TD>Sonac-Gent</TD><TD>1</TD><TD>10</TD><TD>5</TD><TD>20</TD><TD>550</TD></TR><TR><TD>LFI224207</TD><TD>Trob-Dong</TD><TD>3</TD><TD>50</TD><TD>40</TD><TD>1</TD><TD>20</TD></TR><TR><TD>LFI225815</TD><TD>Moyp-Ballym</TD><TD>6</TD><TD>300</TD><TD>250</TD><TD>35</TD><TD>1000</TD></TR><TR><TD>LFI225820</TD><TD>Dunga-Dunga</TD><TD>4</TD><TD>25</TD><TD>10</TD><TD>40</TD><TD>650</TD></TR></TBODY></TABLE><P> </P><P>What I want:</P><P>Combine table 1 and 2.</P><P>In table 2 there are a lot lines with the same supplier.</P><P>But in table 2 there is only 1 active line per supplier.</P><P> </P><P>So i need to combine the active line and get them as above overview.</P><P> </P><P>Can someone help me with this?</P>Sat, 21 Nov 2020 20:30:16 GMTjohnkorten2020-11-21T20:30:16ZMerge 2 tables into 1 overview
https://community.powerbi.com/t5/Power-Query/Merge-2-tables-into-1-overview/m-p/1508866#M46925
<P>Good evening,</P><P> </P><P>I want to merge table 1 and table 2 into 1 overview.</P><P>But there is not a normal relation between the 2 tables.</P><P> </P><P>Sources:</P><P>Table 1</P><TABLE><TBODY><TR><TD>FileID</TD><TD>Supplier</TD></TR><TR><TD>LFI223081</TD><TD>Dunga-dunga</TD></TR><TR><TD>LFI223747</TD><TD>Sonac-Gent</TD></TR><TR><TD>LFI224207</TD><TD>Trob-Dong</TD></TR><TR><TD>LFI225815</TD><TD>Moyp-Ballym</TD></TR><TR><TD>LFI225820</TD><TD>Dunga-Dunga</TD></TR></TBODY></TABLE><P> </P><P>Table 2</P><TABLE><TBODY><TR><TD>Supplier</TD><TD>active</TD><TD>FFA</TD><TD>PH</TD><TD>NPH</TD><TD>PE</TD><TD>Ni</TD></TR><TR><TD>Dunga-dunga</TD><TD>no</TD><TD>5</TD><TD>20</TD><TD>15</TD><TD>39</TD><TD>700</TD></TR><TR><TD>Sonac-Gent</TD><TD>yes</TD><TD>1</TD><TD>10</TD><TD>5</TD><TD>20</TD><TD>550</TD></TR><TR><TD>Trob-Dong</TD><TD>yes</TD><TD>3</TD><TD>50</TD><TD>40</TD><TD>1</TD><TD>20</TD></TR><TR><TD>Moyp-Ballym</TD><TD>yes</TD><TD>6</TD><TD>300</TD><TD>250</TD><TD>35</TD><TD>1000</TD></TR><TR><TD>Dunga-dunga</TD><TD>yes</TD><TD>4</TD><TD>25</TD><TD>10</TD><TD>40</TD><TD>650</TD></TR></TBODY></TABLE><P> </P><P> </P><P>New overview</P><TABLE><TBODY><TR><TD>FileID Supplier</TD><TD>FFA</TD><TD>PH</TD><TD>NPH</TD><TD>PE</TD><TD>Ni</TD></TR><TR><TD>LFI223081</TD><TD>Dunga-dunga</TD><TD>4</TD><TD>25</TD><TD>10</TD><TD>40</TD><TD>650</TD></TR><TR><TD>LFI223747</TD><TD>Sonac-Gent</TD><TD>1</TD><TD>10</TD><TD>5</TD><TD>20</TD><TD>550</TD></TR><TR><TD>LFI224207</TD><TD>Trob-Dong</TD><TD>3</TD><TD>50</TD><TD>40</TD><TD>1</TD><TD>20</TD></TR><TR><TD>LFI225815</TD><TD>Moyp-Ballym</TD><TD>6</TD><TD>300</TD><TD>250</TD><TD>35</TD><TD>1000</TD></TR><TR><TD>LFI225820</TD><TD>Dunga-Dunga</TD><TD>4</TD><TD>25</TD><TD>10</TD><TD>40</TD><TD>650</TD></TR></TBODY></TABLE><P> </P><P>What I want:</P><P>Combine table 1 and 2.</P><P>In table 2 there are a lot lines with the same supplier.</P><P>But in table 2 there is only 1 active line per supplier.</P><P> </P><P>So i need to combine the active line and get them as above overview.</P><P> </P><P>Can someone help me with this?</P>Sat, 21 Nov 2020 20:30:16 GMThttps://community.powerbi.com/t5/Power-Query/Merge-2-tables-into-1-overview/m-p/1508866#M46925johnkorten2020-11-21T20:30:16ZRe: Merge 2 tables into 1 overview
https://community.powerbi.com/t5/Power-Query/Merge-2-tables-into-1-overview/m-p/1508947#M46928
<P>Hi <LI-USER uid="269177"></LI-USER> </P><P>I don't see thelogic of how the Dunga-dunga rows are connetced between the tables. How do I know which row in Table 2 joins to which row in table 1?</P><P>Phil</P>Sun, 22 Nov 2020 00:51:38 GMThttps://community.powerbi.com/t5/Power-Query/Merge-2-tables-into-1-overview/m-p/1508947#M46928PhilipTreacy2020-11-22T00:51:38ZRe: Merge 2 tables into 1 overview
https://community.powerbi.com/t5/Power-Query/Merge-2-tables-into-1-overview/m-p/1509030#M46934
<P>Hello <LI-USER uid="269177"></LI-USER> </P>
<P> </P>
<P>first you need to filter your second table to delete inactive suppliers. As you want to compare this two columns ignoring the case, you need to add a new column where you filter the second table, ignoring the case. Here an example</P>
<LI-CODE lang="cpp">let
Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nHzNDIyNrAwVNJRcinNS0/UTQGRSrE6MDlzE3OgXHB+XmKyrntqXglCysTIACQVUpSfpOuSn5eOkDG1MDQFyvjmVxboOiXm5FTmIssZGcAtc4FYFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FileID = _t, Supplier = _t]),
Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcinNS0/UTQGRSjpKeflAwhSIjQyAhCGIZWwJJMwNDJRidaKVgvPzEpN13VPzSoCClanFIEUgbICszdQUojikKD9J1yU/Lx2u1hgkC1JiYgDVaQRR6ptfWaDrlJiTU5kLV2wG0mAAUmgE1mNsCrYJ6hJUd0N0mIDVwtwDtsMM5JZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Supplier = _t, active = _t, FFA = _t, PH = _t, NPH = _t, PE = _t, Ni = _t]),
FilterTable2 = Table.SelectRows(Table2, each ([active] = "yes")),
JoinTable1WithTable2 = Table.AddColumn
(
Table1,
"Table2",
(row)=> Table.SelectRows(FilterTable2, each if Comparer.OrdinalIgnoreCase([Supplier],row[Supplier])=0 then true else false)
),
#"Expanded Table2" = Table.ExpandTableColumn(JoinTable1WithTable2, "Table2", {"active", "FFA", "PH", "NPH", "PE", "Ni"}, {"active", "FFA", "PH", "NPH", "PE", "Ni"})
in
#"Expanded Table2"</LI-CODE>
<P> </P>
<P>Copy paste this code to the advanced editor in a new blank query to see how the solution works.<BR /><BR />If this post <STRONG>helps</STRONG> or <STRONG>solves </STRONG>your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)<BR />Kudoes are nice too<BR /><BR />Have fun<BR /><BR />Jimmy</P>Sun, 22 Nov 2020 08:29:01 GMThttps://community.powerbi.com/t5/Power-Query/Merge-2-tables-into-1-overview/m-p/1509030#M46934Jimmy8012020-11-22T08:29:01Z