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.
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
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 .
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!
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.
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??😢
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?
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 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.
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)
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.
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
Thanks for providing such a detailed video
Thanks, hopefully it helped.
Thank you Gerard ! .
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 .
Where are you based? Some people needed to change the culture code to local settings
Thank you Gerard. Was waiting for this. 🎉. BEAUTIFULLY explained.
Thank you, appreciated. I had recorded a few days ago, but didn't turn on the mic, so I had to re-record yesterday.
@@dganalysis oh, that's bad, sorry about it.
@@nash_life no worries, life lesson learned 😅
Hello Gerard you are an awesome data master. Thanks
Cheers Joel, yes, you can replace the data culture, depending on what date format you typically use. E.g. US, GB, etc.
Very well documented workflow Gerard ❤
Many thanks!
Amazing work mate!
Cheers 👍
Thank you very much for these video
My pleasure, hope it helps. Are you entering the challenge?
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
Thanks, hope to do stares and NERC regions this evening.
Easier to break into smaller pieces.
Thx uncle geard
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!
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
32:02 did you right this your self or is there a way to generate in visual studio?
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
May I know where can I learn use case of visual studio and power bi?
@vin_k15 I just use it to edit longer code, because I can use find/replace, etc functions then copy the code back in.
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.
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
Waiting for part 2
Should be today
I cant find the excel file used for this class. Would be great to practice along with him.
If you go to mavenanalytics.io they have a data playground that has all the data tables for this
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?
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.
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??😢
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?
My location is Nigeria
@@onlyoneadeleke try "en-ng" instead of "en-gb"
Thank you@@dganalysis. I had to update my regional settings 😊
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!!
Hi there,
Have you removed the spaces and Full stops from the text string?
@@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?
If the time code has hh:mm:sstt, "tt" refers to the AM PM component
@@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.
29:40 what is this tool?
Visual studio code, I think it's free to download with Microsoft
Thank you
I found it so hard to organize the states and event types! There is just no consistency.
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.
Hey @Gerard ,
I got a doubt
What happened at 28:17 exactly
Hi, I tried to update my regional settings in Power BI for Restoration2 column, but still its shows "Record". how to resolve this?
What is your local region /language that you are using?
India
@@aditikathare5219facing the same issue. It converted am/pm to 24 hr. So in some rows it is showing record.
@aditikathare5219 instead of en-GB there might be an equivalent for urdu or hindi date formats
@@dganalysis okay.
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)
Send me a screenshot of the error message when you click on the error - can do it on LinkedIn