Blog

  • Power BI – Recalculate / Refresh a Calculation Group

    In Power BI, when working with calculation groups, they will often need to be recalculated when certain changes are made. You will end up with this error message if you use a calculation group that’s been changed in your Power Bi Report: The query referenced Calculation Group Table ‘Calculation Group’ which does not hold any data because it needs to be recalculated or refreshed.   Thankfully, the solution to this error is quite simple…

    » Read more
  • Exporting to a CSV file from SSMS – Simple BCP Method

    Export table contents to a CSV file from SQL Server  It may be useful to export a tables contents to a CSV file for some exploratory analysis, to feed an ETL and much more. This can be done with some simple SQL commands that exist as a stored procedure, SQL agent job or just a one off piece of code. The export can be the whole table or filtered results but one thing to keep in mind is the number of rows and result size as it may become…

    » Read more
  • @@ROWCOUNT in Synapse Dedicated SQL Pool

    If you’ve been working with dedicated SQL pools in Synapse, you may have noticed that the SQL Server system variable @@RowCount is not supported. I will show you a workaround in synapse to get the same information. First, use the query label option to give your query an identifier that you can use to track it: SELECT * FROM Dim.Date OPTION (LABEL = ‘MyQuery’) You can then use DMVs to find your query using the specified label…

    » Read more
  • What is Microsoft Fabric? (Power BI + Synapse + DW + DataLake + ML)

    What Is Microsoft Fabric

    At today’s Build conference, Microsoft announced Fabric. What is this? In simple terms, think of taking Synapse Analytics, Data Warehousing, Data Lakes, Data Factory, Spark Notebooks and Machine Learning, and bring them all together into Power BI. This is underpinned by Microsoft OneLake, a high performance scalable data lake storage layer, supporting all of the above. OneLake is, as the name implies, one data lake that can be used across…

    » Read more
  • Why Are There Gaps in the Identity Column in My SQL Database?

    All too often there are questions asked about missing records that are identified by gaps in the identity column of a table. If a table, ordered by the identity column, shows “1, 2, 3, 5”, record 4 must have been deleted right? Well, although that is a possibility, it’s not a safe assumption as there are other causes of these “missing” IDs. Let’s start with a simple table, it only has 3 columns, the first of which being an…

    » Read more
  • #This vs @That – Should I use a Table Variable or a Temporary Table?

    When working with SQL Server, it’s not uncommon to need to store data in a temporary table or table variable. While both options can be used to accomplish the same goal, there are differences between the two that can affect performance and the ability to write efficient code. Let’s explore the differences between table variables and temporary tables, and when to use each one. @Table Variables Table variables are declared using the…

    » Read more
  • PySpark Problems: Using Map() gives the error “TypeError: unsupported operand type(s) for /: ‘builtin_function_or_method’ and ‘float’ “

    This error was something I saw at the same time as the error I discussed in my previous blogpost (here), where we are seeing conflicting data types when trying to divide each value of a count of values by the number of days in 3 months (approximately) to get a frequency value over 3 months. I did show the code to fix the error we will discuss in the previous blog post, but I will go into more detail here. The code (without the line that fixes…

    » Read more
  • PySpark Problems: Using Map() gives the error “TypeError: unsupported operand type(s) for /: ‘Row’ and ‘float’ “

    Here’s the first in what will be an adhoc series of short blog posts where I will write a paragraph on the solution to problems I come across when I’m using PySpark. In this post I will discuss using the Map() function to apply a function to every value in an RDD and then getting the error message: “TypeError: unsupported operand type(s) for /: ‘Row’ and ‘float’ “ We see this error because we are…

    » Read more
  • Why should you invest in your team and go to SQLBits?

    A week or two back the entire Purple Frog and Power BI Sentinel team traveled over to Wales to attend SQLBits, which if you didn’t know, is the world’s largest data platform event! This year we had the privilege to go SQLBits wearing two hats; delegate and sponsor – go big or go home, right? I thought I’d share some insights into what I, and the team, thought about spending a week at the ICC Wales with overwhelming amounts of training to…

    » Read more
  • How to add text to images using Python

    In this blog post, I will be showing how to add text to an image in Python. I used this code to generate hundreds of images with different text for one of our clients. The full script is downloadable at the bottom with the required extras. Firstly, install the Python library Pillow. Pillow is a PIL fork, install Pillow using pip and import PIL into the Python script.           In Python, select the image that you would…

    » Read more