Gerard Duggan
Gerard Duggan
  • 33
  • 194 768
Maven Coffee Challenge - Case Sensitivity Issue
The Maven Coffee challenge examines answers to a questionnaire on coffee preferences.
On performing some simple unpivoting of the data, I notices an issue in getting one to many relationships in the data model.
I soon learned there was an issue/conflict that was arising dur to Power Query being case sensitive and Power BI models being case-insensitive. For this challenge, that is a potential issue, as the column that is likely used as the primary key has potential duplicates if you consider the values without case sensitivity.
So, here I take a look at that, and examine a simple workaround using an index column
👺 Data Goblins Article | Case Sensitivity
data-goblins.com/power-bi/case-specific#:~:text=Most%20Power%20BI%20models%20are,a%20case%2Dsensitive%20collation%20property.
#mavencoffeechallenge #mavenanalytics #powerbi #powerquery
📱 SOCIAL MEDIA
LinkedIn: www.linkedin.com/in/gerard-duggan-15420a55/
Twitter: @DGAnalysis
📔 BLOGS & WRITING
Medium:medium.com/@duggangerard
DG-Analysis: dg-analysis.com/
Maven Showcase: mavenanalytics.io/profile/Gerard-Duggan/44421347
Переглядів: 1 258

Відео

Maven Power Challenge - Power Query Clean - Pt2
Переглядів 1,2 тис.7 місяців тому
The Maven Power Challenge has presented a real life case in "dirty data" with power outage data provided between 2002 and 2023. In Part 2 of this cleaning exercise, I look at using Power Query to: - Look at two methods to find individual state names within a free text column - Split column text to rows using split delimiters for NERC Regions - Categorise Alert Criteria types using a hierarchy o...
Maven Power Challenge - Power Query Clean - Pt1
Переглядів 3,1 тис.7 місяців тому
The Maven Power Challenge has presented a real life case in "dirty data" with power outage data provided between 2002 and 2023. In this video, I look at using Power Query to amend and join data from each year period, then go about cleaning and formatting the event date and time and the restoration date and time. I employ a customised M code to extract dates from multiple format types, whether n...
Overlaps, Bullets and Target Charts in Power BI
Переглядів 7 тис.7 місяців тому
A walkthrough on how to take your viz to the next level and create overlap, bullet and target style data viz charts in Power BI using native visuals with some creative DAX, formatting and trusty error bars, so you can include in your next dashboard creation PBIX file is available for download below, and walkthrough includes both horizontal and vertical versions of the visuals Subscribe for more...
Calculating CAGR in Power BI
Переглядів 3,4 тис.8 місяців тому
A look at how to apply CAGR (Compound Annual Growth Rate) calculations in Power BI using tow different formula, and also a dynamic year slicer. Subscribe for more Power BI advanced tips and tricks. 📱 Data Source: Maven Challenges - Apple Greenhouse Emissions www.mavenanalytics.io/challenges/maven-environmental-challenge/27 📱 Original LinkedIn Post: www.linkedin.com/posts/gerard-duggan-15420a55_...
Multi-Layer Slope Charts with zero DAX
Переглядів 6839 місяців тому
Recreating a multilayered slope chart, showing both high level and detailed components and trends. See how to use line style, size and colour to simplify a story and improve communication to the end user. Subscribe for more Power BI advanced tips and tricks. 📱 Data Source: Makeover Monday - Week 33 2023 www.makeovermonday.co.uk/data/ 📱 Original LinkedIn Post: www.linkedin.com/posts/andykriebel_...
Simple Gauge Chart Alternatives in Power BI
Переглядів 12 тис.9 місяців тому
The Gauge Chart is not everyone's "go-to" visual in Power BI. But what if you want to show some kind of scoring value? What can we use? Here, I show some simple alternatives using vertical and horizontal stacked bar charts, reference lines and a few little tricks to give a nice clean finish. Subscribe for more Power BI advanced tips and tricks. 📱 SOCIAL MEDIA LinkedIn: www.linkedin.com/in/gerar...
Super Scorecards in Power BI
Переглядів 6 тис.10 місяців тому
Here, I will show you how to combine a structured excel table and a series of SWITCH/TRUE functions to create a scorecard in Power BI that allows you to show multiple measures and data types in a single column I will also add in some custom icons and colour formatting to give it a professional look. This concept method can then be applied by you in multiple working scenarios to create kpi type ...
Hacking a Donut or Stacked Bar Chart in Power BI to Create a Killer KPI Card
Переглядів 15 тис.10 місяців тому
- Are you looking for ideas to create KPI visuals that measure against a target or the part of a whole, and don’t fancy using the dreaded gauge chart? - Do you want to add in some dynamic measures to provide insightful context? - And do you also want to apply colour formatting and some visual flourishes to set it off? Is the answer is yes, yes, yes, then you have the right video. I am going to ...
Colour Formatting Column Charts Based on Values
Переглядів 4,5 тис.11 місяців тому
In this video, I will walk you through how to create dynamic colour formatting in column charts based on: - Max/min values - above or below and average - above or below a threshold set by a dynamic parameter All formatting takes into account the selected date ranges, and there is even the bonus feature of creating dynamic subtitles to capture key information and provide that added context. The ...
Combining Field Parameters and Conditional Formatting
Переглядів 4,4 тис.11 місяців тому
Here, I will show you how to combine the power of field parameters and colour conditional formatting in Power BI to create dynamic visuals that allow you to select metrics and highlight the top performer in that metric, as well as show how they did in other key metrics. The data model file is linked below if you want to follow along, and the medium link below shows the walkthrough steps and cal...
Next Level KPIs in Power BI
Переглядів 82 тис.11 місяців тому
I am recreating a next level KPI in Power BI using a native line chart visual, just like the one I used in the Maven North Wind Challenge. This involves writing a series of logical formula to take advantage of the conditional formatting of the title and subtitle to provide a dynamic KPI. I have also written an accompanying article in medium to walk through the steps and provide the dax measures...
Book Review - The Wall Street Journal Guide to Information Graphics
Переглядів 700Рік тому
In this video I provide a review of the Wall Street Journal Guide to Information Graphics, a data visualisation book by Dona M. Wong. Links: ft.com/vocabulary (this will redirect to a github page where you can download the chart visuals) Books mentioned: storytelling with data - Cole Naussbaumer Knaflic show me the numbers - Stephen Few Subscribe at: ua-cam.com/channels/B0-lEpIO-Dc20Fc9nGKLtQ.h...
Visualizing the Energy Transition in Power BI
Переглядів 1,3 тис.Рік тому
This is my presentation at the Enterprise DNA summit on data in industries. I gave a brief introduction into what I do, and my lightbulb moment in seeing the importance of data visualisation and analysis. From there, I go into exploring a data set from DNV to see the key trends and insights in the transition from fossil fuel to renewable energy sources. Subscribe at: ua-cam.com/channels/B0-lEpI...
Running Total in Power Query - Maven Pizza Table Utilisation
Переглядів 5 тис.Рік тому
Running Total in Power Query - Maven Pizza Table Utilisation
Likert Scale and NPS Power BI Walkthrough Tutorial
Переглядів 12 тис.Рік тому
Likert Scale and NPS Power BI Walkthrough Tutorial
Maven Unicorns Challenge - Extracting Investor Names in Power Query
Переглядів 1,3 тис.2 роки тому
Maven Unicorns Challenge - Extracting Investor Names in Power Query
Maven Remote Work - Dashboard Walkthrough
Переглядів 3,4 тис.2 роки тому
Maven Remote Work - Dashboard Walkthrough
Power Query - Unpivot Data from Multiple Columns - Maven Super Bowl Challenge
Переглядів 3632 роки тому
Power Query - Unpivot Data from Multiple Columns - Maven Super Bowl Challenge
Power Query - Creating Categories - Maven Super Bowl Challenge
Переглядів 4342 роки тому
Power Query - Creating Categories - Maven Super Bowl Challenge
Simplified Filter Flow in Power BI Models
Переглядів 1,7 тис.2 роки тому
Simplified Filter Flow in Power BI Models
Power Query - Count or Extract Text from List - Maven Challenge
Переглядів 1,2 тис.2 роки тому
Power Query - Count or Extract Text from List - Maven Challenge
Building a Sankey Chart in Power BI with SUMMARIZECOLUMNS
Переглядів 7 тис.2 роки тому
Building a Sankey Chart in Power BI with SUMMARIZECOLUMNS
6 Simple DAX Measures to Build a Social Media Dashboard in Power BI
Переглядів 5 тис.2 роки тому
6 Simple DAX Measures to Build a Social Media Dashboard in Power BI
Nested IF-AND-OR Statements in Power Query
Переглядів 4,9 тис.2 роки тому
Nested IF-AND-OR Statements in Power Query
Maven Taxi Challenge Final Dashboard
Переглядів 7512 роки тому
Maven Taxi Challenge Final Dashboard
Maven Taxi Challenge - Power Query Cleaning Plan and Execution
Переглядів 3,2 тис.2 роки тому
Maven Taxi Challenge - Power Query Cleaning Plan and Execution
Highest Scoring English Premier League Players
Переглядів 1512 роки тому
Highest Scoring English Premier League Players
Who are the Highest Scoring Team in English Premier League History?
Переглядів 1232 роки тому
Who are the Highest Scoring Team in English Premier League History?
What will Northern Irelands population demographics look like in the future?
Переглядів 1,9 тис.2 роки тому
What will Northern Irelands population demographics look like in the future?

КОМЕНТАРІ

  • @garudabowo
    @garudabowo 9 годин тому

    Amazing way to display KPI. Thank you for sharing

    • @dganalysis
      @dganalysis 3 години тому

      @@garudabowo my pleasure

  • @nyamdulamnyamjav9700
    @nyamdulamnyamjav9700 2 дні тому

    my if and does not work

  • @benimatrix
    @benimatrix 9 днів тому

    Very awesome technique, Thank you so much.

  • @sarveshmore2645
    @sarveshmore2645 13 днів тому

    Thanks Gerard! Great Tutorial keep uploading!

  • @albedoguardian875
    @albedoguardian875 14 днів тому

    Underrated Gem 💎

    • @dganalysis
      @dganalysis 14 днів тому

      Thanks, appreciate it 👍

  • @eliezerreyes2767
    @eliezerreyes2767 14 днів тому

    excelent!!

  • @arvindverma2629
    @arvindverma2629 18 днів тому

    nice explantion

  • @Gandalf1969Guy
    @Gandalf1969Guy 19 днів тому

    Love it!

  • @andypand25
    @andypand25 22 дні тому

    Any clarification on how i copy across the measures for each column

  • @petertrnka459
    @petertrnka459 27 днів тому

    crazy guy...fantastic!

  • @DK-dc1mc
    @DK-dc1mc 27 днів тому

    Really a nice one❤

  • @alexgry4763
    @alexgry4763 28 днів тому

    Gerard looks and sounds very similar to Jason Isaacs. So all this video is pretty much Lucius Malfoy explaining you some Power BI

    • @dganalysis
      @dganalysis 28 днів тому

      Haha, thanks, I'll take it as a compliment....Jason Isaacs is a legend 🙌

  • @gurvindersingharora5301
    @gurvindersingharora5301 Місяць тому

    Hello Sir, When to combine ALL function with Calculate + Filter function in DAX in detail ? Please make a video of it 🙏 ❤

  • @aspirethrissur4016
    @aspirethrissur4016 Місяць тому

    Great Video. Thank You Dear Gerard.

    • @dganalysis
      @dganalysis Місяць тому

      Thanks, much appreciated

  • @davoud6487
    @davoud6487 Місяць тому

    Promo>SM

  • @alexsk8ish
    @alexsk8ish Місяць тому

    Such a great method. I was thinking how could I achieve measuring customer flow in a span of 1h and always felt that I was missing something. Thank you Gerard!

    • @dganalysis
      @dganalysis Місяць тому

      Thanks, glad it helped

  • @musariololade2204
    @musariololade2204 Місяць тому

    Fantastic work. Learned so much from the hack. Your explanation and simplicity of writing the DAX measures really made me understand a lot. Kudos. Looking forward to learning more from you. Would be applying this on a live project in the coming days.

  • @haingo1252
    @haingo1252 Місяць тому

    Amazing way to display KPI. Thank you for sharing

  • @hellmutmatheus2626
    @hellmutmatheus2626 Місяць тому

    Really thank you!!!!!!

  • @365cloudhouse7
    @365cloudhouse7 Місяць тому

    Unpivot!!! Did use it once a hundred years ago, but I really didn't remember it. You are a hero! Thanks!!!

  • @logocreative1694
    @logocreative1694 2 місяці тому

    Hi Gerard I want your little help in power bi If i create matrix table Those matrix table columns have names of employees and also manager's name now when i publish report when employees see that report they should see only their name The manager's name should not appear when the manager see the report Then everyone's name should appear It possible to we can create such a button in Power Bi, when the manager presses that button, a panel of password will appear and after entering the password, a report of employees include with manager names will appear.

  • @Markiska75
    @Markiska75 2 місяці тому

    Thank you so much for such a useful video! Allowed our company to skip purchasing likert scale visual subscription.

    • @dganalysis
      @dganalysis 2 місяці тому

      Thanks, great to hear!

  • @marcelobrito9452
    @marcelobrito9452 2 місяці тому

    thank you so much, you helped me alot

    • @dganalysis
      @dganalysis 2 місяці тому

      Happy that it helped

  • @stephhogan6070
    @stephhogan6070 2 місяці тому

    Thank you for sharing! On the last method, I could not get my % at the bottom to start with 0. I also only had negative, unsure, and positive.

  • @vinipowerbi
    @vinipowerbi 2 місяці тому

    Hi Gerard, what is your design inspiration?

    • @dganalysis
      @dganalysis 2 місяці тому

      For which project?

    • @vinipowerbi
      @vinipowerbi 2 місяці тому

      @@dganalysis for current challenge going on sales one and also in general. How do you choose which chart to use and design report layout?

  • @ifeanyicharlesokafor9104
    @ifeanyicharlesokafor9104 2 місяці тому

    I cant find the excel file used for this class. Would be great to practice along with him.

    • @dganalysis
      @dganalysis 2 місяці тому

      If you go to mavenanalytics.io they have a data playground that has all the data tables for this

  • @BbabbittGolf
    @BbabbittGolf 2 місяці тому

    love this kpi hack!! Out of curiosity - what canvas size do you use? I’m having a hard time fitting everything using 16:9

    • @dganalysis
      @dganalysis 2 місяці тому

      Thanks, typically I set a custom of 1600:900. Considering the smallest font size is 8, I find 16:9 can be a bit restricting

  • @chandanamb9061
    @chandanamb9061 2 місяці тому

    Hi, Thank you for the video Had a doubt here your are only comparing end of month... What if I had to select the daterange that the user has selected in slicer maybe 2 weeks and based on that show the trend by comparing it with the previous 2 weeks...

    • @dganalysis
      @dganalysis 2 місяці тому

      Thanks, If you wanted to be able to show trends on a two-week /fortnight basis, you will probably need a dedicated column in the date table to show the fortnight values. Then the process would be quite similar

  • @shololamayowamicheal4586
    @shololamayowamicheal4586 3 місяці тому

    Hi Gerard, I Love the insight, I had a challenge of not copying and pasting it.

    • @dganalysis
      @dganalysis 3 місяці тому

      Thanks, glad it helped. Maybe I should try and update this.

  • @user-hz6gw3uk6z
    @user-hz6gw3uk6z 3 місяці тому

    Have gone back to this video again and again these last few months. Very informative. Very helpful. Sometimes your voice fades so I think you have a tendency to be soft spoken :). But one of the best, if not the best step-by-step walkthroughs for making a divergent Likert scale. 'Appreciate the fact that it brings together all the arguments on how best to visualize this kind of data. Thank you very much! :)

    • @dganalysis
      @dganalysis 3 місяці тому

      Thank you, yes my microphone wasn't that great on some of my earlier videos, and I am also not a naturally loud talker. But glad it has helped, thanks for the feedback, appreciate it.

  • @sowjanyap3267
    @sowjanyap3267 3 місяці тому

    Amazing Video, I followed you step by step and it really helped me build a Multi KPI Dashboard. Thank you so much!

  • @logocreative1694
    @logocreative1694 3 місяці тому

    Which bubble chart are you using for this data

    • @dganalysis
      @dganalysis 3 місяці тому

      I used a deneb custom visual for this one.

    • @logocreative1694
      @logocreative1694 3 місяці тому

      Where did i get this codes

    • @dganalysis
      @dganalysis 3 місяці тому

      @@logocreative1694 vega.github.io/vega-lite/examples/

    • @logocreative1694
      @logocreative1694 3 місяці тому

      Thank you

  • @luisfernandomacedo1451
    @luisfernandomacedo1451 3 місяці тому

    Great one! Have you tried making it with Dax?

    • @dganalysis
      @dganalysis 3 місяці тому

      Thanks, I haven't revisited it to work with dax. I think it would be possible with two measures, one picking up each point, and one picking up only the max and min point

    • @luisfernandomacedo1451
      @luisfernandomacedo1451 3 місяці тому

      @@dganalysis Yep! A few days ago I had a meeting at the company I work for. Users' requirement was to create a slope chart for price in the begin and end of YTD. In the middle if it - broken down by category, must be displayed the %var. I guess its doable to get it!

  • @Mr97Frog
    @Mr97Frog 3 місяці тому

    May I know the color scheme for your pbi?

    • @dganalysis
      @dganalysis 3 місяці тому

      There is a link to download the file. You should see the colour scheme in there.

  • @syedmahiyan5839
    @syedmahiyan5839 3 місяці тому

    Audio is very low for me to hear.

  • @workstuff5253
    @workstuff5253 3 місяці тому

    Not sure if it was just me, but found the audio a little quiet.

    • @dganalysis
      @dganalysis 3 місяці тому

      Not sure, I thought it was OK, but will check it out

  • @anindyachakraborty9294
    @anindyachakraborty9294 3 місяці тому

    Amazing!

  • @vinayak254
    @vinayak254 3 місяці тому

    Thanks Mr. Duggan. It's a great video

    • @dganalysis
      @dganalysis 3 місяці тому

      You are very welcome

  • @mohamedelsherbieny4199
    @mohamedelsherbieny4199 3 місяці тому

    Wow, this is impressive. Thank you.

  • @priyojitdey1865
    @priyojitdey1865 3 місяці тому

    Are we keeping the missing values? Or removing them?

    • @dganalysis
      @dganalysis 3 місяці тому

      When you unpivot, null values are removed. If you are talking about the main table, it depends, but you could remove line items that don't provide any information, but you may want to calculate the % of all people surveyed who drink coffee, for example - depends what information you want to communicate

    • @priyojitdey1865
      @priyojitdey1865 3 місяці тому

      Thanks a lot for the suggestion.

  • @navyanagavlli7406
    @navyanagavlli7406 3 місяці тому

    Thank you sir

  • @nash_life
    @nash_life 3 місяці тому

    Great video Gerard, Thank you. I was just thinking what would happen if we encountered the same issue in transactional databases. This highlights how important it is to have numeric or alphanumeric keys as unique identifiers.

    • @dganalysis
      @dganalysis 3 місяці тому

      Yes, I am thinking back that I may have encountered something like this before, but didn't notice. But usually if I get a many:many relationship in a table that I think should be distinct it throws up a 🚩 But yes, highlights importance of having complete distinction, maybe choose either lower or upper case, but not both.

  • @thebobbieleehubbard
    @thebobbieleehubbard 3 місяці тому

    This is exactly what I was looking for. Liked and subscribed, my friend. Thank you!

    • @dganalysis
      @dganalysis 3 місяці тому

      Thanks, happy it helped. If there is other content you are looking for, let me know.

  • @wwpharmacist
    @wwpharmacist 3 місяці тому

    Great video, what if we make the submission id (UPPERCASE) will this resolve the duplication issue

    • @dganalysis
      @dganalysis 3 місяці тому

      Unfortunately not, but it would identify the duplicates in the submission ID quite easily. An index column is probably the easiest solution, but you could look at adding random characters to the existing ID as an alternate solution. Cheers

  • @johnsteedman7937
    @johnsteedman7937 3 місяці тому

    Thanks Gerard, I have now used the Disconnected Table idea at work and believe it will be much appreciated.

    • @dganalysis
      @dganalysis 3 місяці тому

      Thanks, happy to hear it has helped

  • @DrewMery
    @DrewMery 4 місяці тому

    The UNICHAR(10) is not producing a new line. Anyone else having trouble with this?

    • @dganalysis
      @dganalysis 4 місяці тому

      It only works with the subtitle (not title), and ensure your version on power bi has been updated within the last year.

    • @DrewMery
      @DrewMery 4 місяці тому

      @@dganalysis I just figured out the problem. I had text wrap on. I had to turn it off.

    • @dganalysis
      @dganalysis 4 місяці тому

      @@DrewMery all good 👍

  • @nikhildhiwar2355
    @nikhildhiwar2355 4 місяці тому

    Wow!!! Thank you so much. This opened a whole new perspective for the table visual. Is there any way to shift the sorting arrow of the column header from left to, right? It's very confusing for the end user if the column headers are right aligned and the sorting arrows are at the left-hand side.

    • @dganalysis
      @dganalysis 4 місяці тому

      I'm not sure about that one. I've not seen any option to do that

  • @mohammadzeeshansiddiqui2731
    @mohammadzeeshansiddiqui2731 4 місяці тому

    Very informative video thanks for sharing it, just one issue the voice quality was poor

    • @dganalysis
      @dganalysis 4 місяці тому

      Thanks, my old microphone wasn't so good.

  • @luigisir6893
    @luigisir6893 4 місяці тому

    Great!!!

  • @thembamngomezulu5215
    @thembamngomezulu5215 4 місяці тому

    Pleas help I'm trying to create an Overlapping Bar Chart for our Company Vehicles, Comparing Budgeted_km vs Actuals_km Fixed_Fuel_effecincy vs Actual_Fuel_effecincy Bugeted_Diesel vs Actual_Diesel...How can I use your DAX Function to perfom this for my Vehicle Anaylises...Thanks Gerard.

    • @dganalysis
      @dganalysis 4 місяці тому

      Did you download the file for this video?