Automate Power BI single table refresh using Azure Synapse Analytics and Power BI APIs
August 23rd, 2023 in Azure, Blog, Power BI, Synapse, TabularIn a blog I posted back in June 2022, I explained how you could trigger a Power BI dataset refresh via Azure Synapse Analytics. https://www.purplefrogsystems.com/2022/06/how-to-trigger-a-power-bi-dataset-refresh-via-azure-synapse-analytics-in-3-simple-steps/ However, what if you just wanted to refresh a select number of tables or objects rather than a full dataset? This might be the case if you are performing intraday loads in your ETL. There…
» Read moreMicrosoft Fabric – So how do I build a Data Warehouse without Identity Columns and Merge?
June 29th, 2023 in Blog, Data Warehouse, Fabric, SQLAt the time of writing (29th June 2023) the following T-SQL commands are not supported in a Fabric Data Warehouse. Identity Columns Merge To see the full list of T-SQL limitations please refer to the documentation link below: https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area Here at Purple Frog, all of our Data Warehouses are knitted together using surrogate keys to link Dimension tables to Fact tables following the…
» Read moreExcel, CSV and Leading Zeros
March 3rd, 2023 in Blog, ExcelYes, even at Purple Frog Systems we use Excel! Excel is great for that quick and dirty analysis, but there is nothing worse than opening a CSV file directly with Excel and finding out you’ve lost all of your leading zeros. Take a look at my Sales.csv file Viewed as a CSV in Notepad++ it looks as it should… However viewed directly using Excel… It now looks like I’ve got a sales quantity of 62 for product id 5! To solve…
» Read moreAzure Data Factory Pricing – How much is my pipeline actually costing me?
December 1st, 2022 in ADFv2, Azure, Blog, Data FactoryHas a client ever asked you how much it actually costs to run a single pipeline in Azure Data Factory? Have you ever thought ADF pricing is just a black box? Well, hopefully my latest blog post will give you an indication on how you can start calculating the cost of a pipeline run! I will base my analysis on a sample pipeline containing the following activities as shown below: 1 x Lookup Activity (Pipeline Activity) 1 x Copy Data Activity (Data…
» Read morePrevious Row Value DAX
October 18th, 2022 in Blog, Business Intelligence, DAX, Power BIIn this quick blog post I’m going to show you how to use a hidden DAX function to get a previous row value with just a few lines of code. It’s that hidden that intellisense doesn’t even recognise it! I’ll use an example of some fictional race results from some well-known cartoon characters! Lets assume that in this scenario we wanted to analyse the time difference between 1st and 2nd place, 2nd and 3rd place etc in the race scenario…
» Read moreHow to Trigger a Power BI Dataset Refresh via Azure Synapse Analytics in 3 Simple Steps.
June 27th, 2022 in Azure, Blog, Business Intelligence, Power BI, SynapseIn this blog post we’ll explore how to trigger a Power BI Dataset via Azure Synapse Analytics in 3 simple steps: Step 1: Create a new security group called “AzureSynapsePowerBIIntegration” in your Azure Active Directory. Next add the name of your Azure Synapse Workspace as a member of your new security group. This will be your service principal. Step 2: Login to powerbi.com and in the top right hand corner locate “Settings” and…
» Read moreHow to parameterise the Execute Pipeline activity in Azure Synapse Analytics.
April 12th, 2022 in Azure, Blog, Data Factory, Synapse, UncategorizedUnfortunately, as of April 2022 there is not an option to parameterise or add dynamic content to an “Execute Pipeline” activity to invoke a pipeline run. However, with the use of a Microsoft API there is method which we can use to overcome this. In this blog post we’ll use the “Pipeline – Create Pipeline Run” API documented below: https://docs.microsoft.com/en-us/rest/api/synapse/data-plane/pipeline/create-pipeline-run…
» Read moreHow to resolve SSIS “Row yielded no match during lookup” error.
January 26th, 2022 in Blog, Business Intelligence, SSISHave you ever been faced with the SSIS error “Row yielded no match during lookup”? If so, this blog is for you! A customer of ours recently faced the same issue in one of their SSIS packages and asked us to investigate this for them. Initial investigations on their side highlighted that when they replicated the lookup component using a standard join in T-SQL (similar to the image below) it returned the expected results. So why was SSIS…
» Read moreReplicating SQL windowing functions in Power Query.
October 1st, 2021 in Blog, Power BI, Power QueryI’m sure you’ve all heard of windowing functions in SQL Server? But what if you need to replicate these windowing functions in Power Query? Here I have a sample CarSales.csv dataset of car sales per brand, per year, per month and the number of units sold. I will use this dataset to demonstrate how we can replicate the windowing LAG function in Power Query. In my example I’d like to calculate the month on month sales growth per car…
» Read moreHow to change the summarization of multiple columns in Power BI.
August 17th, 2021 in Blog, Power BIHave you ever been faced with the issue where Power BI automatically adds a sigma symbol to the front of your column if it detects it’s a data type of integer or decimal? Now this isn’t too much of an issue if you’ve imported only a few columns but what if you’ve imported lots? Going through this column by column can become tiresome! Luckily there is a hack, not only to turn summarization to “None” on multiple columns at once, but…
» Read more
Call today 0845 643 6463
