A Fine Slice Of SQL Server
Imke Feldmann has an interesting solution to a text search problem in Power Query:
In the native function, you have to pass in a record with search term and column name. So if you search for “blue” in column “Description”, your formula would look like so:
Table.Contains( YourTableName, [Description = "blue"] )But that’s not what I want in this case. I want the formula to search through all columns within the table for the occurrence of “blue”.
Read on to see how to do this.
Comments closedDustin Ryan gives us a Power BI dashboard covering NFL draft history:
With the 2019 NFL Draft upon us, of course I wanted to visualize some NFL draft data in Power BI. So I put together this interesting set of visualizations based on some data I scraped from Pro Football Reference. The dataset includes drafts from 1936 to 2019 including picks through round 6 where applicable. I’ll update the dataset as the remaining rounds of the 2019 draft are completed. So feel free to take a look, interact with the dashboards, and let me know if you have any questions.
Click through for the dashboard.
Comments closedErik Svensen shows how you can migrate a Power BI data model up to Azure Analysis Services:
After the Azure Analysis Services web designer was discontinued per march 1 2019 – link – there is no official tool to do a move of a PBIX datamodel to Azure Analysis Service. But by using a few different tools we do have ways of doing it anyway.
Click through for the step-by-step instructions.
Comments closedIf you’re working with large data or complex queries that take a long time refresh, cancelling one of those refreshes can even take longer time, especially, if the query has run for quite some time already.
Luckily, there is an easy trick to cancel refresh without loosing the work you’ve done already
Read on to see how.
Comments closedI have written a few articles in the past that toy with the ideas of changing visibility and text colour based on selection. I started to wonder if it was possible to make a visual appear (or not) based on a selection from the user. There is no out of the box way to do that today. It is possible to use bookmarks to show an hide an object, but the user must click a specific button to do this. I want the user to be able to interact with a report and see (or not see) a chart based on some valid selection across the report. Microsoft is already working on building expression based formatting across the breadth of Power BI however as of now the only item you can change is the header in a chart.
Hopefully this gets better over time.
Comments closedBrad Llewellyn shows how you can take Spark MLlib results and feed them into Power BI:
MLlib is one of the primary extensions of Spark, along with Spark SQL, Spark Streaming and GraphX. It is a machine learning framework built from the ground up to be massively scalable and operate within Spark. This makes it an excellent choice for machine learning applications that need to crunch extremely large amounts of data. You can read more about Spark MLlib here.
In order to leverage Spark MLlib, we obviously need a way to execute Spark code. In our minds, there’s no better tool for this than Azure Databricks. In the previous post, we covered the creation of an Azure Databricks environment. We’re going to reuse that environment for this post as well. We’ll also use the same dataset that we’ve been using, which contains information about individual customers. This dataset was originally designed to predict Income based on a number of factors. However, we left the income out of this dataset a few posts back for reasons that were important then. So, we’re actually going to use this dataset to predict “Hours Per Week” instead.
Check it out. And Brad’s not joking when he says the resulting model is terrible. But that’s okay, because it was never about the model.
Comments closedChris Web looks into what changing the Power BI Dataflow container size does for us:
Currently there is no way to change this 256MB in Power BI Desktop or Excel although someone has already posted a suggestion on the Ideas site to allow us to change it. How much of an impact does this actually have on refresh performance though? Without the ability to change this setting it’s hard to say, but I suspect it could be significant and that a lot of Power Query performance problems could be explained by this behaviour.
The situation is different in the Power BI service, where I understand there is a limit on the overall amount of memory that a single Power Query query evaluation can use.
Read on to understand the differences here between running on Power BI Desktop and running in the Power BI service, as well as a bit of testing on Chris’s part.
Comments closedThese measures produce the correct figures month by month. However, at the year level the number of working days might be too big whenever there are incomplete months – this always happens before the end of the year, or when the first month with sales is not January as in the following example. As you can see in the following figure, in this case the yearly value of SalesPerWorkingDays is lower than the value of any month, which does not make much sense.
Click through for the solution.
Comments closedLeila Etaati shows how you can use R to export data from Power Query to disk or to SQL Server:
There is always a discussion on how to store back the data from Power BI to local computer or SQL Server Databases, in this short blog, I will show how to do it by writing R scripts inside Power Query.
Leila also describes a complication you may hit where writes happen twice.
Comments closedDavid Eldersveld shows off how you can customize maps in the Power BI Report Builder Map Gallery:
One of the defining features of Power BI’s [paginated] Report Builder vs the current geospatial offerings in Power BI Desktop is the native support for ESRI Shapefiles. If you have worked with maps in Report Builder over the years, you may have used the Map Gallery. The Map Gallery offers a collection of built-in geographies, but you can also customize and enhance what’s available out of the box.
David takes us through an example of simplifying one map, but the same technique can help in other ways.
Comments closed