![]() You’ll notice it found 227 matches out of the total 627 items in the Dimension Stock Item table This is the one we created a few steps ago. Select the Fact Sale (2) table in the dropdown. Select the Stock Item Key column in the window. Power Query will probably tell you in the lower left you have 227 rows.įrom the Dimension Stock Item Table, click on the Merge Queries button on the Home Ribbon. Right-click on the Stock Item Key and select Remove Duplicates. This step isn’t necessary, but it makes the query logic a bit easier to understand. Right-click on the Stock Item Key field in this query and select Remove Other Columns. This table will not be loaded to the data model. Right-click on that newly created query and make sure it “Enable Load” is not checked. To do this, right-click on the Fact Sales table, and create a reference. Inner Join to Limit Records in the Dimension Stock Item Tableįirst, we need a unique list of items from the Fact Sales table to work with. So any slicers or filters would have 400 unnecessary items. There are 627 items in the Stock Item table, but only 227 unique items in the Fact Sales table. For both example, you’ll need to connect to the Fact Sales and Dimension Stock Item tables. There is a Power BI file attached below but you can easily follow along in a new PBIX file for these examples. See this video for quick instructions on getting SQL Server and some test databases up and running. ![]() If you are a Power BI developer and don’t have a local copy of SQL Server and some test databases on your machine, you are doing yourself a disservice. I’ll be using the WideWorldImportersDW database. Let me walk you through both methods so it is clear. ![]() And what makes it so powerful is not just it’s utility, but when you run it against data in a SQL Server or similar server, Power Query will fold the statement. The most straight forward way to do this is by doing an Inner Join between the tables, but there is another way, using the powerful List.Contains() feature of Power Query. So I’ll filter the customer table so it only includes what I would call “active customers” that are shown in the sales table. The same would apply if there was no slicer, but the consumer wanted to filter using the Filter pane. I don’t like loading up a slicer with dozens or hundreds of items that have no corresponding records. This can happen because the sales data is limited to recent years, or specific regions, for a given report. For example, a customer dimension table might have 1,000 customers in it, but the sales fact table may only have 400 customers in it. In order to make a clean user experience for Power BI report consumers, I will limit the data in my Dimension tables to only include data that is in the Fact tables.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |