Maven Power Challenge - Power Query Clean - Pt1

Поделиться
HTML-код
  • Опубликовано: 11 янв 2025

Комментарии • 63

  • @MargaretEfron
    @MargaretEfron Год назад +1

    Thank you so much for your video. This is a great intro to Power Query Editor, which I've used some for Power BI projects, but not for intensive data cleaning like this.

    • @dganalysis
      @dganalysis  Год назад

      Thanks, my pleasure.
      There are a few complex problems in here. There are always a ton of approaches people can take, but happy to share a few options.
      Finished the second video last night, will post today

  • @jattocollins9414
    @jattocollins9414 Год назад +1

    Thanks for providing such a detailed video

    • @dganalysis
      @dganalysis  Год назад

      Thanks, hopefully it helped.

  • @archanapattanaik8713
    @archanapattanaik8713 Год назад +1

    Thank you Gerard ! .

    • @archanapattanaik8713
      @archanapattanaik8713 Год назад

      Hi Gerard,
      I tried using your Restoration2 code, but it wasn't successful for me .I I used the CULTURE for the dates "nl-NL" for my country and getting RECORD .

    • @dganalysis
      @dganalysis  Год назад

      Where are you based? Some people needed to change the culture code to local settings

  • @nash_life
    @nash_life Год назад +1

    Thank you Gerard. Was waiting for this. 🎉. BEAUTIFULLY explained.

    • @dganalysis
      @dganalysis  Год назад +1

      Thank you, appreciated. I had recorded a few days ago, but didn't turn on the mic, so I had to re-record yesterday.

    • @nash_life
      @nash_life Год назад

      @@dganalysis oh, that's bad, sorry about it.

    • @dganalysis
      @dganalysis  Год назад +2

      @@nash_life no worries, life lesson learned 😅

  • @joeltarazonr
    @joeltarazonr Год назад

    Hello Gerard you are an awesome data master. Thanks

    • @dganalysis
      @dganalysis  Год назад +1

      Cheers Joel, yes, you can replace the data culture, depending on what date format you typically use. E.g. US, GB, etc.

  • @wwpharmacist
    @wwpharmacist Год назад +1

    Very well documented workflow Gerard ❤

  • @shahzadkheros
    @shahzadkheros Год назад

    Amazing work mate!

  • @xiamojq621
    @xiamojq621 Год назад

    Thank you very much for these video

    • @dganalysis
      @dganalysis  Год назад +1

      My pleasure, hope it helps. Are you entering the challenge?

  • @wwpharmacist
    @wwpharmacist Год назад

    I can't wait for part 2 😍😍
    every tip and trick has been noted down, and added to my tool skill🦾🦾
    many thanks Gerard.
    Walid

    • @dganalysis
      @dganalysis  Год назад +1

      Thanks, hope to do stares and NERC regions this evening.
      Easier to break into smaller pieces.

  • @COUNCILOFFIGURES
    @COUNCILOFFIGURES 3 месяца назад +1

    Thx uncle geard

  • @luisfernandomacedo1451
    @luisfernandomacedo1451 Год назад +1

    Nice job done on the restoration col. Do you have the M script posted for handling the messy date the way you did anywhere? I'd like to understand it better!

    • @dganalysis
      @dganalysis  Год назад +1

      Link is in the video notes - Google drive.
      I'll maybe post the full editor version for all steps this evening when I get back from work

  • @vinipowerbi
    @vinipowerbi Год назад +1

    32:02 did you right this your self or is there a way to generate in visual studio?

    • @dganalysis
      @dganalysis  Год назад +1

      I wrote the code myself, but used alot of copy-paste. There is a link to the file in the video notes. You can download and review the code

    • @vinipowerbi
      @vinipowerbi Год назад

      May I know where can I learn use case of visual studio and power bi?

    • @dganalysis
      @dganalysis  Год назад

      @vin_k15 I just use it to edit longer code, because I can use find/replace, etc functions then copy the code back in.

  • @grow-with-abi
    @grow-with-abi Год назад +1

    Hi Gerard, for extracting the states from Affected Area, I tried to use this DAX, "Extracted State=
    CALCULATE (
    MAX('State Names'[State]),
    FILTER (
    'State Names',
    CONTAINSSTRING(Full_table[Area Affected], 'State Names'[State] & " ") ||
    CONTAINSSTRING(Full_table[Area Affected], " " & 'State Names'[State]) ||
    CONTAINSSTRING(Full_table[Area Affected], 'State Names'[State])
    )
    )"
    is there any possible way to extract from abbreviation , I tried some combinations with OR logic in the same but it did'nt worked.

    • @dganalysis
      @dganalysis  Год назад

      Hi there, it's not something I have attempted to do before, so I'm not sure to be honest - good luck, will be happy to know if you work it out

  • @AnjaliDashora
    @AnjaliDashora Год назад +1

    Waiting for part 2

  • @ifeanyicharlesokafor9104
    @ifeanyicharlesokafor9104 9 месяцев назад +1

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

    • @dganalysis
      @dganalysis  9 месяцев назад

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

  • @alexrosen8762
    @alexrosen8762 Год назад +1

    Hi Gerard,
    Great stuff but how can I get access to the working files? Do I have to have a membership to get access to them?

    • @dganalysis
      @dganalysis  Год назад

      If you go to maven analytics website, and visit their data challenge page, you should be able to download the challenge docs. It's all free.

  • @onlyoneadeleke
    @onlyoneadeleke Год назад +1

    Hi Gérard,
    Thanks for this video.
    But I keep getting "Record" error anytime I apply the m-code to the custom column(restoration 2).
    I've made sure the restoration 1 column is clean,all extra spaces and periods removed but I keep getting the "Record" error.
    What could be wrong??😢

    • @dganalysis
      @dganalysis  Год назад

      The "Culture" for the dates might not match your local settings.. I used "en-GB". You might need to replace with your own date format. What is your location?

    • @onlyoneadeleke
      @onlyoneadeleke Год назад

      My location is Nigeria

    • @dganalysis
      @dganalysis  Год назад

      @@onlyoneadeleke try "en-ng" instead of "en-gb"

    • @onlyoneadeleke
      @onlyoneadeleke Год назад

      Thank you​@@dganalysis. I had to update my regional settings 😊

  • @grow-with-abi
    @grow-with-abi Год назад +1

    Hi Gerard,
    After applying the DAX code for custom column 2 (Restoration2) , AM PM is not available in the output. Is there any solution for this? Thanks in advance!!

    • @dganalysis
      @dganalysis  Год назад +1

      Hi there,
      Have you removed the spaces and Full stops from the text string?

    • @grow-with-abi
      @grow-with-abi Год назад

      @@dganalysis yup, I have removed the space and full stops. The output have no AM or PM added to the time stamp. Am I missing out something sir?

    • @dganalysis
      @dganalysis  Год назад +1

      If the time code has hh:mm:sstt, "tt" refers to the AM PM component

    • @grow-with-abi
      @grow-with-abi Год назад

      @@dganalysis My bad, it's my regional settings, resolved it. Thank you so much for these videos Gerard, I've followed your video and cleaned the data, I'm a beginner in Power BI, glad I found your channel.

  • @vinipowerbi
    @vinipowerbi Год назад +1

    29:40 what is this tool?

    • @dganalysis
      @dganalysis  Год назад +1

      Visual studio code, I think it's free to download with Microsoft

    • @vinipowerbi
      @vinipowerbi Год назад

      Thank you

  • @MargaretEfron
    @MargaretEfron Год назад +1

    I found it so hard to organize the states and event types! There is just no consistency.

    • @dganalysis
      @dganalysis  Год назад

      I'll drop that video today, should help with the states. The events are a little more difficult. It depends on how you wish to categorise.

  • @h.a.r.i.
    @h.a.r.i. Год назад

    Hey @Gerard ,
    I got a doubt

    • @h.a.r.i.
      @h.a.r.i. Год назад

      What happened at 28:17 exactly

  • @aditikathare5219
    @aditikathare5219 Год назад

    Hi, I tried to update my regional settings in Power BI for Restoration2 column, but still its shows "Record". how to resolve this?

    • @dganalysis
      @dganalysis  Год назад

      What is your local region /language that you are using?

    • @aditikathare5219
      @aditikathare5219 Год назад +1

      India

    • @vinipowerbi
      @vinipowerbi Год назад

      ​@@aditikathare5219facing the same issue. It converted am/pm to 24 hr. So in some rows it is showing record.

    • @dganalysis
      @dganalysis  Год назад +1

      @aditikathare5219 instead of en-GB there might be an equivalent for urdu or hindi date formats

    • @aditikathare5219
      @aditikathare5219 Год назад +1

      @@dganalysis okay.

  • @jezie75
    @jezie75 Год назад +1

    I've used this formula to make the "Time of Restoration" column, but keep getting an error
    = Table.AddColumn(#"Added Custom1", "Time of Restoration", each if [Restoration2] = null then null else if
    Text.Contains([Restoration2], ":") then Time.From
    (DateTimeZone.From[Restoration2]) else null)

    • @dganalysis
      @dganalysis  Год назад

      Send me a screenshot of the error message when you click on the error - can do it on LinkedIn