-
Microsoft Fabric Part 3. Taskmaster Project. Adding Dimensions: Tasks, Episodes and Date to Notebooks and creating Delta PARQUETÂ and PARQUET Files
In Parts 1 and 2, we did a lot of analysis on the data. Understood Contestants and the data between the csv files attempts and people. Its now time to create some of the other dimensions we need for the project. Back to our Taskmaster Notebook to see what other data is available to use.… Read more
-
Microsoft Fabric Part 2. Taskmaster Project. Updating a dim Contestants table in Notebook and Delta PARQUET File
This blog is the next step for Microsoft Fabric Part 1. Taskmaster Project. Creating a dim Contestants table in Notebook and Delta PARQUETÂ File In Part 1, a Contestants Dimension was created from the csv file attempts csv. The data from this was transformed and added into a PARQUET file and a DELTA PARQUET file so… Read more
-
Microsoft Fabric Part 1. Taskmaster Project. Creating a dim Contestants table in Notebook and Delta PARQUET File
Its so exciting at the moment with all the changes going on in the world of Microsoft and I wanted to use the trial version of Fabric to see if I could move my ‘standard’ working practices over into Fabric. The first thing was to find a practice dataset for this exercise so of course… Read more
-
Azure Synapse – Creating a Type 1 Upload with Pipelines
Working through exercises and learning paths for Microsoft Synapse is a really good way of becoming familiar with concepts and tools. And whilst these are a fantastic source of learning. the more you do, the more questions you end up asking, Why use this option over this option for example? SO, lets look at one… Read more
-
Power Query Editor – Avoiding the creation of all the helper Queries and Folders in power BI
A current project being worked on starts with a folder in Sharepoint with multiple files that need to be imported together (All with the same Spec) There are lots of groups of files to import into Power BI Get Data > Folder creates helper folders that clutter up the back end and aren’t the best… Read more
-
Power BI. Using flags in a junk dimension
We currently have a project where the metrics are actually flags to count whether a record is true or false rather than business metrics like Amount, SaleAmount etc Etc. Now, if you left these in the fact table you could sum them to create a measure. But you are left with thousands of rows of… Read more
-
Power BI Concatenating two measures together under one column header for a table display
Another requirement came through for a table that showed the current month figures against last months figures, However, to save space the two values were in the same column, last month contained in a () So part one was to create the date time intelligence for last month, And then to attempt to concatenate the… Read more
-
Power BI Composite Modelling (Multiple datasets Report) – Couldn’t load the data for this visual
This blog is for anyone using the new App experience (August 22) and has created a report using multiple datasets and the users can’t see the data We have The app is published but the users only see visuals with no data. When they try to refresh they see this error This seems to be… Read more
-
Power BI – Deployment Pipeline Quick Tips – Setting dataflow Environment sources and Publishing (Direct Query) Datasets containing multiple Datasets
You need Premium or Premium Per user to work with Deployment Pipelines This happens right at the beginning of the Deployment Pipeline process when you have just added Dev to the Pipeline and you need to deploy Test and Prod Tip – Changing Data source Rules for Dataflow You now need to deploy your dev… Read more
-
Power BI – App Viewers can’t see the data in the report
We recently had an issue where a shared dataset (pbix) had been set up over a SQL Database. This was then published to Power BI A new pbix was created. Power Platform – Power BI datasets was chosen and the shared dataset was selected. Then reports were created and published to Service. An App was… Read more
-
Power BI Datamarts (New May 2022)
Difference between dataflows, datamarts and datasets Datasets Lets have a quick look at the history of the data set Here we see everything in one pbix file. Only one person can work with the file at any one time. We cant reuse anything or work on anything separately. Our dataset is in the one pbix… Read more
-
Azure Logic App – Copying a file from Sharepoint to a Data Lake
I have been asked to set up a Logic app in Azure (That is Power Automate for anyone outside Azure) to copy specific file(s) from a Sharepoint folder and add to an Azure Data Lake. The first example file is around 16,00 rows and not likely to grow too significantly. This is the same with… Read more
-
Power BI February 2022 Updates Dynamic M Query Parameters
Now supports SQL Server and more data sources But what are Dynamic M Query Parameters and what does this mean? It feels like they have been upgraded to use with direct query data sources so you can restrict the amount of data being asked for at the lowest level. Lets have a look at a… Read more
-
Power BI Admin APIs to return a list of email subscriptions
Get Dashboard Subscriptions brings back a list of everyone who has subscribed to a dashboard What is a Power BI Subscription? Subscriptions are a great way to assign yourself and other users to get emails regarding report content. There are certain governance rules we follow. Report viewers views content via an app. We don’t want… Read more
-
Power BI Sparklines
Sparklines are small charts you can add to a table or Matrix and are new for the start of 2022. Above we have a Matrix showing Products by country and I would like to also see this measure on a timeline. Select the metric Value : Add a Sparkline Button Here we choose the measure… Read more
-
Investigating the Power BI Scanner APIs Part 1
Delegating Permissions Part 1 will be an introduction to Scanner APIs and how you delegate permissions in order to now have to use the power BI Admin Account Power BI Scanner APIs are fairly new and there have been a lot of updates happening in the September October 21 Power BI Updates. These scanner APIS… Read more
-
Power BI DAX – Create a percentage across days of week (ALLEXCEPT. ALL. DIVIDE)
We just looked at the following request We have sold specific quantities of products. Can we look at a week for a year in a slicer and see the percentage of items sold for each day. Which days do we sell the most products overall? We have the following model (Just the parts that will… Read more
-
Power BI AI Visual Smart Narratives
Power BI has given us the following AI visuals: Key influencers Q&A Visualisation Decomposition Tree And now we also have smart narratives to work with. Many companies want to create report that includes text in regards to the report they are looking at to help the user understand the key takeaways. This usually involves someone… Read more
-
Power BI October 21 Updates GetUserArtifactAccessAsAdmin API through to PowerShell
There have been some new API updates (September October Power BI 2021) and I thought I would see what they were all about and how you can use them starting with the API mentioned in the October updates. I haven’t used these APIs before and I thought it would be interesting to see how the… Read more
-
Power BI – reporting on Items NOT in a filter
We are working on a report to look at Power BI activities and objects at the moment. Lets have a look at the model This is a test model with a date dimension (When the Activity occured) A Report dimension (Details of the report used in the activity) A Dataset dimension (Details about the dataset… Read more
-
Azure SQL Database Dev to Production Part 4
I have had quite a lot of issues in regards to the whole dev to prod process with the SQL database. my last attempt which I wrote in this blog worked well until I shut the project down. Then once reopened I would always lose my Project or GIT, so I went back to the… Read more
-
Power BI Premium Deployment Pipelines and Sub Workspaces – Publishing a live report to a pro workspace from a premium workspace
I wanted to have a look at a specific set up we have in regards to our Power BI Workspaces and apps We have Power BI Premium P1 Node We use dataflows We use a Deployment Pipeline for Dev Test and Production Premium Workspaces We have a scenario where we have a sub workspace with… Read more
-
Power BI Can you use an inactive join for a slicer (USERELATIONSHIP)
Imagine the scenario. You are dealing with two dates. date A and B. You create an active and an inactive relationship to your date table. You can create Dax with USERELATIONSHIP for any measures that use the inactive join. Great for the bar charts, line charts etc. But what if you have a slicer for… Read more
-
Power BI. Created Reports in your ‘shared dataset file’ and now want to move them to a report pbix file. How do you go about this task?
The recommended approach to Power BI as an enterprise solution is to have your ‘shared data set in a pbix file where you can just create test visuals to check everything is ok. Then create the real reports in a separate pbix file live connected to the dataset. But what happens if you create great… Read more
-
Microsoft Business Applications Summit 2021. Power BI Vision and Roadmap
Driving a Data Culture This is the 6th year of Power BI. Power BI has always been about driving data culture and has been rapidly evolving throughout its data journey. Power BI is top of the Gartner Magic Quadrant and growing exponentially across users and business. Lets have a look at where we have come… Read more
-
Autoscaling with Power BI Premium Gen 2
We have been working with Power BI Premium for a few weeks. Simply switching it on and seeing how it goes. However, Its worth paying some attention to Autoscaling that you can get with Premium Gen 2 (Preview) Auto Scaling Previously our Power BI Premium capacity could struggle when there was high capacity. For example… Read more
-
Quick Guide of Power BI Premium Features
It can be quite infuriating to be a Power BI Pro user and constantly be on the verge of doing something, before realising that its only available with Premium. Thank goodness for Power BI Premium Per User for smaller companies. At just double the price per user licence, all these enterprise level goodies can be… Read more
-
Power BI March 2021 Updates – Direct Query for Power BI datasets and Analysis Services, Testing the new fixes.
Direct Query for Power BI datasets and Analysis Services feature has currently been in preview for a couple of months Switching this feature to on, should hopefully allow us to create reports over multiple data sets, which will allow use to have smaller datasets, maybe just over a single model. These can then be connected… Read more
-
Power BI external Tools – ALM Toolkit
How do you do schema changes to your model without refreshing the entire model? This is especially applicable for large models using incremental refresh where refreshing the entire data set takes a long time to process. The ALM toolkit really helps with this process The examples I am going to use aren’t incremental refresh but… Read more
-
Power BI Key Influences Error – A field in explain by is not on the ” table or a table from the same data source and related to it by a many to one relationship. Try Summarising it
The analysis is performed at the record level of the ‘table’ A field in explain by is not on the ” table or a table from the same data source and related to it by a many to one relationship. Try Summarising it This model is all about behaviour and attitudes for an event company… Read more
-
Power BI Dev to Prod without using Deployment Pipelines
Power BI Pipelines are a Premium Service which allows you to set up a development, QA and Production workspace and easily send your development changes across to the workspaces. Each workspace can also be configured to work with different data sources. for example, your Dev Power BI may sit on top of test data, rather… Read more
-
Power BI Admin. Increasing awareness when publishing a report – December 2020 Updates
It is very easy for Power BI Developers to create and then publish reports to the service without much thought. It will be different from company to company, but it would be great to remind developers each time they publish that there are certain standards that need to be maintained for each published report With… Read more
-
Power BI when to (Possibly) use a Bi Directional Filter in your data model
Power BI Loves a Star Schema and It loves single direction joins, one to many from your dimension (Description) tables to your central fact (Business metrics) table The reason for the single direction filter join? You want to be able to filter your measure by descriptors. Sales by Product category. Quantity Sold by Business Unit.… Read more
-
Azure Data Factory Moving from development and Production – Part 2. Using Key vault for Linked Services
In Azure Data Factory Moving from development and Production We looked at how we can use Azure DevOps to move the Json Code for Development Data Factory from development to Production. Its going well, I have however been left with an issue. every time I move into Production details for the Linked Services have to… Read more
-
Power BI Premium Per User now in preview (December 2020 updates)
One of the main benefits of Power BI Premium is the ability to share with hundreds of users who don’t all have to have their own Power BI Pro license at £7.50 a month. At a certain level, Premium becomes less costly than giving everyone who needs them Pro licenses. its said, you need 500… Read more
-
Azure SQL Database. Publishing from Development to Production Part 2
The Dev to Prod Process Initially in part one we set up the process with Visual Studio 2019 and Devops and moved all our objects across to Production. Then with Data Factory we moved data from the production data lake into Production Azure SQL DB We have all the Source data in a data lake… Read more
-
Power BI Premium Gen 2 First Look
Currently Premium Gen 2 is in Preview (Jan 20201) but it looks really exciting for those with Premium capacity. Lets have a look at Gen 2 and see how it differs to Gen 1 Gen 1 With Premium Gen 1 we are bound by the number of vCores and by the memory we have. Lets… Read more
-
Power BI – December 2020 – Small Multiples
Small Multiples are now part of some of the main visuals so lets take a look at them using Adventureworks data To take advantage of this at present you have to switch it on in Preview features in Options / Preview Features Column Using the Clustered Column Chart Note that there is a small multiples… Read more
-
Azure Data Factory – Moving from Development to Production
When working on larger projects we need to merge changes from Developers. When all the changes are in the central branch we can then have an automated process to move development to Production Smoke tests In computer programming and software testing, smoke testing is preliminary testing to reveal simple failures severe enough to, for example,… Read more
-
Power BI November 2020 release anomaly detection new AI visual
Last month we had smart narratives which was amazing. This month we get another AI feature. Anomality detection. So lets have a look at how this works. Its still in preview at the moment so make sure you go to File > Options and Settings > Options to try it out First, lets create a… Read more
-
Data Factory, Moving multiple lookup worksheets from Excel to one lookup table in SQL Server
A current project has an xlsx containing around 40 lookups in individual worksheets Each worksheet consists of a code and a description We decide that we want every single lookup in one lookups table in SQL Server. This will have a Lookup Name, Code and Description that we can then use for the rest of… Read more
-
Power BI September 2020 Updates Total Labels for Stacked visuals
We recently had a requirement for a visual for a stacked column chart with a grand total Our stacked chart contains: Axis – Month year Legend – Category Values – Metric To get this working we had to create a line and stacked column chart. The line was hidden so we just had the data… Read more
-
Power BI Updates September 2020 – Mini blog – Maintain layer order
To get an understanding of this new feature lets have a quick look at a chiclet it currently has a rectangle shape behind it. If you click on the rectangle and it becomes highlighted, the shape is brought to the front so we cant see the chiclet So every time you hover over the report… Read more
-
Power BI September 2020 Updates: Smart Narratives
We have been hearing so much about this, I am very excited for Smart narratives Smart narratives offer insights into your visuals which are also customisable Lets use the Adventureworks demo report to try it out Because its still in preview you will need to turn it on in Options and settings > Options Add… Read more
-
Power BI Dataflows, Computed Entities and Query Folding
When you create a dataflow in Power BI Service without using Premium capacity you may hit issues Like ‘cant refresh dataflow Linked Entities are when you link objects from different dataflows within Power Query Editor. to create transformations over a linked entity you need to create a computed entity that references the linked entity. There… Read more
-
Power BI Dataflow issues. Let the whole dev team know
Currently, if your dataflow fails the only person who will be notified is the owner of the dataflow. We want all our developers within the team to know. There doesn’t appear to be any way to do this at the moment but there is a workaround that was suggested to me by on the Power… Read more
-
Where to create your reports. Power BI Service or the Power BI Desktop
Since starting to use Power BI we have been advocating the following ‘basic’ model However. You can also create reports within Power BI Service. This is mentioned within the certification route for DA 100 Analysing Data with Power BI. Which makes things a little more confusing and I have been asked on lots of occasions.… Read more
-
Power BI August 2020 Updates Perspectives for Personalised visuals in Preview
Personalised visuals have been out for a while (The ability for report consumers to change the visual to more suit their needs), but we need to make sure that users aren’t over whelmed by field options. Lets look at a quick demo using Adventureworks data. First of all, you need to make sure Personalised Views… Read more
-
Power BI. How to connect up to a sharepoint folder containing multiple files using a dataflow
We have a current use case where we have a Sharepoint folder that will contain monthly files. We want to create a dataflow that contains everything that we need to pull into a shared data set The point of this is to have one data set filtered to the correct folder you wish to use.… Read more
-
Power BI Updates August 2020 Organisational visuals in Power BI Admin
Power BI report developers can either use the default Power BI visuals, or go to AppSource to pick up visuals. Some of these visuals are available within Power BI certified, which means that these visuals have been through rigorous tests and have passed all the requirements. But there are many other visuals you can use.… Read more
Follow My Blog
Get new content delivered directly to your inbox.
Debbies Microsoft Power BI, SQL and Azure Blog 