{"id":899,"date":"2021-01-25T18:21:37","date_gmt":"2021-01-25T16:21:37","guid":{"rendered":"https:\/\/www.flip-design.de\/?p=899"},"modified":"2021-01-25T18:21:37","modified_gmt":"2021-01-25T16:21:37","slug":"incomplete-data-at-a-self-service-bi-project-with-power-bi","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=899","title":{"rendered":"Incomplete Data at a self service BI project with Power BI?"},"content":{"rendered":"\n

Mostly at self service BI Projects the business users imports data from unproven sources \u2013 like Excel. But if you have a project and\/or source like this, data can be have problems, because at a CSV or Excel file the data format can be anything.
When you take look at following screenshot. There a two tables. One has the facts (sales per date and branch). The second table provides the branches and here, I\u2019ve added a row with the ID \u201c-2\u201d, this provides as a key, which doesn\u2019t exists at the most sources. This key can be used to map error rows.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

So, if you import the data, everything is fine and you can make insights of it:<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

But after some time, it can be happen that user insert some data which can be not used by the data model:<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

Then you get following error when you refresh the data and the row will not be loaded and the end-users thinks everything is fine.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

To avoid this problem you can edit your Power Query ETL process and replace this error by set the \u201c-2\u201d<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

Now, you will get the row and will mapped to the Key from the dimension<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

To notice the users or to build up a QA dashboard, you can write some DAX code to count the errors and give some notice to the users<\/p>\n\n\n\n

Missings =
\u00a0\u00a0\u00a0 var missings = CALCULATE(COUNTA(Facts[BranchId]), FILTER(Branch, Branch[BranchId] = -2 ) )
\u00a0\u00a0\u00a0 var message = IF(missings > 0, \"Some branches are have incomplete data\", \"\")
\u00a0\u00a0\u00a0 return message<\/code><\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

Mostly at self service BI Projects the business users imports data from unproven sources \u2013 like Excel. But if you have a project and\/or source like this, data can be have problems, because at a CSV or Excel file the … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/899"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=899"}],"version-history":[{"count":1,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/899\/revisions"}],"predecessor-version":[{"id":907,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/899\/revisions\/907"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=899"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=899"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=899"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}