{"id":85347,"date":"2019-10-03T20:46:52","date_gmt":"2019-10-03T20:46:52","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=85347"},"modified":"2024-09-03T20:15:21","modified_gmt":"2024-09-03T20:15:21","slug":"merging-tables-in-power-bi-dataflows-with-pro-subscription","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/merging-tables-in-power-bi-dataflows-with-pro-subscription\/","title":{"rendered":"Merging tables in Power BI dataflows with PRO subscription"},"content":{"rendered":"<p><strong>Power BI<\/strong> dataflows is a powerful self-service <strong>ETL<\/strong> tool. However, some features demand the use of <strong>Power BI Premium<\/strong>, way more expensive than the PRO version and sometimes inaccessible.<\/p>\n<p>One of these tasks, for example, is the merge of two tables. Even in the same dataflow, if you have two tables and make the merge, the resulting table will be a calculated table and this requires <strong>Power BI Premium<\/strong> for the refresh.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-85348\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/10\/image4.png\" alt=\"requires premium\" width=\"1309\" height=\"281\" \/><\/p>\n<p>We could argue the data should be better prepared in Data Warehouse, but this is not always true. The merge of two tables is a so simple task that even having a good DW as a source you may need it.<\/p>\n<p>Let&#8217;s say we have two tables, <strong>Customers<\/strong> and <strong>Geography<\/strong>. Customers table has a <strong>GeographyId<\/strong> field which identifies the location of the client.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-85349\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/10\/image9.png\" alt=\"initial state\" width=\"264\" height=\"206\" \/><\/p>\n<p>In order to make the merge between <strong>Customers<\/strong> and <strong>Geography<\/strong> we need to follow these steps:<\/p>\n<ul>\n<li>Edit the query for the <strong>DimCustomer<\/strong> table<\/li>\n<li>Click on the <strong>&#8216;&#8230;&#8217;<\/strong> button, <strong>&#8220;Combine Tables&#8221;<\/strong> menu option, <strong>&#8220;Merge Queries&#8221;<\/strong><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-85350\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/10\/image8.png\" alt=\"merge queries\" width=\"565\" height=\"374\" \/><\/p>\n<ul>\n<li>In the <strong>Merge<\/strong> window that will be opened, select the 2nd table as <strong>DimGeography<\/strong><\/li>\n<li>Again in the <strong>Merge<\/strong> window, select <strong>GeographyId<\/strong> field in the <strong>DimCustomer<\/strong> table<\/li>\n<li>One more time, in the <strong>Merge<\/strong> window, select <strong>GeographyId<\/strong> field in the <strong>DimGeography<\/strong> table<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-85351\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/10\/image7.png\" alt=\"Merge Window\" width=\"806\" height=\"798\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>After the <strong>Merge<\/strong> window updates itself, click <strong>Ok<\/strong> button<\/li>\n<li>In the <strong>DimCustomer<\/strong> table, find the <strong>DimGeography<\/strong> field and click the expand button<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-85352\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/10\/image6.png\" alt=\"expand table\" width=\"229\" height=\"224\" \/><\/p>\n<ul>\n<li>Select the fields you would like to include in the <strong>DimCustomers<\/strong> table and click the <strong>Ok<\/strong> button<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-85353\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/10\/image5.png\" alt=\"Selecting Fields\" width=\"450\" height=\"469\" \/><\/p>\n<ul>\n<li>Close and save the query<\/li>\n<\/ul>\n<p>Making the merge between <strong>Customers<\/strong> and <strong>Geography<\/strong> will turn <strong>Power BI Premium<\/strong> into a requirement to refresh the dataflow.<\/p>\n<p>In order to work around this problem, there is a simple solution: we can edit the query again, right-click the table <strong>Geography<\/strong> and disable the load option.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-85354\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/10\/image3.png\" alt=\"load table\" width=\"314\" height=\"373\" \/><\/p>\n<p>When this option is disabled, the merge is not considered as a calculated table, allowing the refresh with a Power BI Pro account, no need for Power BI Premium. Another result is that we will not see the <strong>Geography<\/strong> table available in the dataflow anymore, only the <strong>Customers<\/strong> table.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-85355\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/10\/image1.png\" alt=\"single table\" width=\"251\" height=\"125\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Power BI dataflows is a powerful self-service ETL tool. However, some features demand the use of Power BI Premium, way more expensive than the PRO version and sometimes inaccessible. One of these tasks, for example, is the merge of two tables. Even in the same dataflow, if you have two tables and make the merge,&#8230;&hellip;<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,159166],"tags":[4472,4824,101611,101612],"coauthors":[6810],"class_list":["post-85347","post","type-post","status-publish","format-standard","hentry","category-blogs","category-powerbi","tag-dataflow","tag-etl","tag-power-bi","tag-self-service-etl"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85347","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=85347"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85347\/revisions"}],"predecessor-version":[{"id":85356,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85347\/revisions\/85356"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=85347"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=85347"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=85347"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=85347"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}