So how does this relate to error tables? Like most well-documented APIs, the U.S. Census Bureau API has a page devoted to listing and describing all the possible response codes that can be returned by their service. I take this information and build an internal table within the query that defines and describes these response codes in my own words. I’m now able to throw custom messages that make the difference between a
400response code and a404response code more obvious.For example, in the code below, I use the
Error.Recordfunction to create individual records that allow me to catch these unsuccessful requests and throw my own custom error messages to the user. I then create an extra field in each record called ‘Status’, which maps each HTTP response code returned by the API to a corresponding error message of my choosing:
There’s a bit of work, but the end result is a fairly simple explanation for end users.
Marc Lelijveld takes us through the different ways we can embed Power BI dashboards:
In the end of 2018 Microsoft announced a great new feature which allows you to secure embed Power BI content to client applications or sites and keep security still in place. Actually, it almost works the same as the publish to web feature, but then users have to log-in in the embedded frame before they see the content.
Not only to the security to access the report is in place now, also all other security features will still work. This means that Row Level Security can be still in place in case when you are using secure embedding. A lot of people online are really enthusiastic about the new features. Simply because it gives you the ability to embed your content on your website, web portal or wherever you want, without risking security issues.
Click through for discussion on the four techniques.
Kellyn Pot’vin-Gorman hits the Go Faster button:
The gist of this kit is that it is a database repository as part of the sp_BlitzFirst to collect monitoring alerting and performance metric data. Once you’ve set this up, then you can use a Power BI desktop dashboard as an interface for all that data.
Now this is an awesome way to introduce more DBAs to Power BI and it’s a great way to get more out of your metrics data. The challenge is, it’s a lot of data to be performing complete refreshes on and the natural life of a database like this is growth. The refresh on the static database I was sent by Tracy, once I connected my PBIX to the local db sources, took upwards of an hour to refresh. Keep in mind, I have 16G of memory, 32G of swap and have upped my data load options in Power BI quite high.
Kellyn walks through the things she did to improve performance as a starting point, so check it out and be aware that there’s even more that can be done.
Why Python?
I prefer it to R mostly because I don’t have to create the csv-file(names) in advance before I import data to it. This is particularly important for scenarios where I want to append data to an existing file. The key for this task is NOT to use the append-option that Python offers, because M-scripts will be executed multiple times and this would create a total mess in my file. Instead I create a new file with the context to append and use the Import-from-folder method instead to stitch all csvs back together. Therefore I have to dynamically create new filenames for each import. So when the M-Python-scripts are executed repetitively here, the newly created file will just be overwritten – which doesn’t do any harm.
Click through for the code as well as a few caveats.
Alberto Ferrari wants to show sets of contiguous months using DAX:
Today I woke up with an interesting question, about how to show a selection of months in a nice way, detecting contiguous selection. You can easily understand the desired solution from the following figure:
I enjoyed writing a quick solution, which is worth sharing. The code is somewhat verbose, but this is mainly for educational purposes (meaning I did not want to spend time optimizing it). I will likely write a full article on it, for now, just enjoy some DAX code:
I removed the image, but to get the gist (and get you to click through to see it in its beauty), it reads “January, March-April, August-December”
Click through for Alberto’s quick-and-dirty solution and then Chris Webb’s improvement.
Meagan Longoria shows us how to set the tab order for our Power BI elements:
Tab order is the order in which users interact with the items on a page using the keyboard. Generally, we want tab order to be predictable and to closely match the visual order on the page (unless there is a good reason to deviate). If you press the tab key in a Power BI report, you might be surprised at the seemingly random order in which you move from visual to visual on the page. The default order is the order in which the visuals were placed on the page in Power BI Desktop, or the last modified order in PowerBI.com if you have edited your report there.
I had not actually tried to tab through a dashboard in Power BI, so this is news to me.
Wolfgang Strasser wraps up his 24 days of PowerPlatform series:
When I had the first idea of this blog series, it was just a vision – and now – at the end of this series I hardly cannot believe that I kept up and wrote those 24 posts.
It was
… more work than expected,
… more feedback than expected and
… it was more fun than expected!
Click through for 23 links grouped by topic.
Matt Allington shows us one technique to get the latest version of a file using Power Query:
This pattern is common if your new file contains a superset of all the data. It could be a transactional file that grows in length each time or it could be a dimension/lookup table (such as Customers) that can change slowly over time, and you always want to see the latest version. My advice to all my Power Query students is “zero touch the file”. In other words, your objective should always be to have the absolute minimum amount of interaction with the source files possible and push all the work into Power Query. This will minimise the amount of work/rework you have to do in the future. Thinking about the use case here “load the latest version of a file”, the question becomes “how can I make this zero touch”? There are a few issues to consider including naming/renaming of the file and also archiving old copies of the file. This doesn’t sound like zero touch to me.
Read the whole thing and check out the video.
Teo Lachev walks us through conditional formatting in Power BI:
If you have used SSRS, you know that paginated reports are very customizable, and you can make almost any property expression-based. Power BI is yet to deliver expression-based properties to change settings based on runtime condition, such as to change the font style based on the actual value or user selection. Currently, there are two places where you can use a DAX measure for expression-based formatting of colors:
Click through to see those two places. I think I’d like to see conditional formatting be a bit easier than that.
Annie Xu walks us through a couple of financial calculations and how to implement them in DAX:
The Excel XNPV function is a financial function that calculates the net present value (NPV) of an investment using a discount rate and a series of cash flows that occur at irregular intervals. Calculate net present value for irregular cash flows. Net present value. =XNPV (rate, values, dates)
The Excek XIRR(Internal Rate of Return) is the discount rate which sets the Net Present Value (XNPV) of all future cash flow of an investment to zero. If the NPV of an investment is zero it doesn’t mean it’s a good or bad investment, it just means you will earn the IRR (discount rate) as your rate of return. =XIRR(values,dates,guess)
Click through to see how to do this in DAX, especially if your data is not in exactly the right format.
Kevin Feasel
2019-01-18
Error Handling, Power BI
No Comment