How to Think & Write Complex M Easily || Power Query Case Study

Рет қаралды 18,786

Күн бұрын

Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
In this video, we will explore how to think and write complex M in Power Query while tackling a data cleansing problem. Whether you encounter a similar problem or not, you will find valuable insights and techniques that you can apply to your own data sets.
Through this M language solution, we will discuss the logic and steps involved in problem-solving.
Solutions by other people on LinkedIn 💡 - rb.gy/qmcm71
#powerquery #mlanguage #datacleansing #excel #datamanipulation #powerbi #mcode #advancedexcel #datatransformation #problemsolving
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨‍🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File ⬇️ - goodly.co.in/how-think-write-...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!

Пікірлер: 52

@vinovici3204 4 күн бұрын

What a guy. Man I appreciate you and these videos.

@tinhoyhu 7 ай бұрын

This is magic. Not only is the build process magical, but the clear explanation tying it all together.

@pk231 7 ай бұрын

You are really great bruh. I have worked with much complex case and most of the time I have solved it using the UI part with different combination since I am unaware of the power of M. After watching your watching now my brain whenever there is a requirement it thinks how to solve it in M rather than using UI. Great thanks for you!!!

@chrism9037 7 ай бұрын

You are a PQ genius, you really are. Thanks Chandeep!

@pthapa55 7 ай бұрын

Amazing example, thanks for bringing this with us.

@tak0331 7 ай бұрын

Thanks Chandeep for breaking down your problem solving approach and steps. Indded, sharing your logic provided even more value than the solution.

@michaelt312 7 ай бұрын

Another great video. Gets me reconsidering how I handled some steps in the Call Center dashboard I just finished.

@jerrydellasala7643 7 ай бұрын

Big thumbs up! Great solution, great explanation! 👍👍

@SamehRSameh 7 ай бұрын

M-code is magic , need more🎉

@efficiency365 7 ай бұрын

Nested knowledge! Superb. Write a book 🚀

@_SachinKhanna 7 ай бұрын

Ek Number bhai

@meganathanve444 7 ай бұрын

Thanks Chandeep, this is excellent.

@davidmaldonadocastillejos3179 7 ай бұрын

Beautiful logic applied

@KuldeepSingh-nq1vi 7 ай бұрын

Really you Genius at Power Query.. ❤

@JJ_TheGreat 7 ай бұрын

2:12 At first, I had thought that this was an easy problem to solve - that all you have to do is use the user interface and split columns by the comma “,” delimiter - but into rows instead of columns. However, then I tried that - and I realized how stupid I was for thinking that. Removing the commas and getting each day as a separate record is not only confusing, but it also does not help with getting the Boolean values correct (again, more confusing)… Now I will have to logically think about algorithms to solve this (knowing that this will involve writing M code, after I figure it out). Chandeep, you have humbled me!

@user-rp1ub1qw6m 3 ай бұрын

Great Video , Thanks Chandeep.

@shantanudeshmukh4390 3 ай бұрын

Just brilliant !!

@user-ef4oc3vc6r 7 ай бұрын

Thank you very much. Very interesting.

@yewchoob85 5 ай бұрын

Thanks for another great video. I followed along with this example and after completing the exercise I opened the whole Query in Advanced Editor and noticed the code was quite messy. When it comes to using Python I find it easy to break things up and add notes to the script for other users to understand it. With this script, as it uses the brackets like excel to perform functions from inside out, I think it's quite hard for entry level users to read. #"Added Custom" = Table.AddColumn(Source, "Custom", each Record.Combine(List.Transform(Text.ToList(Text.Replace([Days], ",", "")), This line for example, you'd have to add a note to say that the first function Record.Combine is actually happening after everything else has taken the values, stripped commas, converted to list, then records and combined. Anyway, bit long winded but my question is, how would you write this code neatly to allow people who aren't so familiar with M get to grips with the code?

@patrickharilantoraherinjat2994 3 ай бұрын

Awesome. Thankz bro

@rrrraaaacccc80 5 ай бұрын

Great 💯👍

@murmursoy 7 ай бұрын

Great vid as always. 👍 My Solution: // List-to-rows -> pivot -> rename column names let table1 = , table2 = , dayslistadded = Table.AddColumn(table2, "DaySplit", each Text.Split([Days], ",")), dayslisttorows = Table.ExpandListColumn(dayslistadded, "DaySplit"), // Once use table1[Letter] and you have the perfect order without any absent columns! pivoted = Table.Pivot(dayslisttorows, table1[Letter], "DaySplit", "DaySplit", each if _ = {} then null else true), // Sadly not applicable if you have same [Days] values ... roworderreset = Table.Sort(pivoted, each List.PositionOf(table2[Days], [Days])), weekdaynameexpanded = Table.RenameColumns(roworderreset, Table.ToRows(table1)) in weekdaynameexpanded

@rickychen1086 7 ай бұрын

Nice thought process! I would assume it will be easier to achieve the same result using DAX, and Using M vs DAX is something that I always think about/struggle with when tackling a problem. Appreciate it if you could do a video about that thanks

@kebincui 7 ай бұрын

Super.👍👍

@Rice0987 7 ай бұрын

Ok, big Boss! Next time i wanna see how you move that tables on the screen with your fingers!🤗

@ericrobbins6953 7 ай бұрын

What sort of wizardry is this? Incredible

@vibhor1234567890123 7 ай бұрын

Genious, I have been struggling to generate multiple columns in a single step, turns out I need to follow the records approach. Thanks

@kundanbhardwaz6363 7 ай бұрын

Hello vibhor I think i need your assistance in power query

@nitheshn7019 7 ай бұрын

You are awesome bro

@junaidshaiks1296 7 ай бұрын

Thank you for another great video and if possible please make videos on power automate.

@ExcelWithChris 7 ай бұрын

Genius as always. Thanks from South Africa!!

@GoodlyChandeep 7 ай бұрын

Thanks Chris

@jianlinchen7978 2 ай бұрын

wonderful

@andreascharalambides4412 7 ай бұрын

Nice one, Sharing your logic provides huge value. Any black Friday sales on the courses?🙂

@hussainmeghani4809 7 ай бұрын

Please make short video on PQ editor formating...always confused in brackets

@muralichiyan 7 ай бұрын

Nice thanks for that.... could you plz do same this in SQL end

@HappyAnalysing 7 ай бұрын

Hi sir, Your videos are so informative and very helpful to many people like me. Thank you so much for making such an awesome videos. Could you please do a video on org chart on power bi for HR data when there are 30+ employees involved in 4-5 levels. Please help.. I’ve been searching a lot on this and didn’t find any helpful article or video on this. Could you please do a video on this. This will be very helpful. Thank you!

@GoodlyChandeep 7 ай бұрын

Sure!

@williamarthur4801 7 ай бұрын

Another gem full of idea, BTW, I recreantly mentioned transforming one column by values in another, which I couldn't' find a way with tabel.transform, so came up with replace, this could get more complex but as a starting point ; Four columns with letters and number and don't want an extra calc col. Table.ReplaceValue ( Source, each [Col4] , (A)=> if Value.Type(A) = type number then [Col4] * [Col1] else [Col4] , Replacer.ReplaceValue, { "Col4"} )

@kundanbhardwaz6363 7 ай бұрын

I have this same problem have you got the solution

@williamarthur4801 7 ай бұрын

@@kundanbhardwaz6363 the only way I've come up with as posted is to use replace, so I have eg colums of text an number, and I want to multiply number in columnD by ColumnA, transfrom wont work but you can use something like this; Table.ReplaceValue( Source, each [ColD] , each if Value.Type( [ColA] ) = type number then [ColA] * [ColD] else [ColD] , Replacer.ReplaceValue, { "ColD"} ) i have not figured out what the first each [Column] is doing but it wont work without it.

@bulbulahmed3098 7 ай бұрын

❤❤❤

@user-jy2xi9qw2m 7 ай бұрын

Hey Chandeep! Any way you can make a video explaining the ins and outs of when to use curly brackets vs. square brackets in M language???

@GoodlyChandeep 7 ай бұрын

kzfaq.info/get/bejne/a9loftxm15qXpX0.html

@stefankirst3234 7 ай бұрын

Insanity! How do you come up with this stuff? Thanks for another amazing video!

@user-vv6px7gt6q 6 ай бұрын

How to pull data correctly if Thursday letter is kept as 'T' rather 'R'? As lookup source will be same for both Tuesday and Thursday. Please provide this lookup solution, i'm facing this problem and lookup chooses very first option only as i know.

@hussainmeghani4809 7 ай бұрын

Sir, while click on content... Detail displayed but file name cloning remove.... How to fix the problem... I am extract csv format file through power query

@GoodlyChandeep 7 ай бұрын

Download the file ⬇ - goodly.co.in/how-think-write-complex-m-power-query-case-study/ Solutions by other people on LinkedIn 💡- rb.gy/qmcm71 Check out the M Language course ↗ - goodly.co.in/learn-m-powerquery/

@flyingHubby 4 ай бұрын

I really love your videos, but this solution is over complicated. This does it all and uses pretty simple UI commands. - split to list - expand - join on the lookup column - add "true" column - pivot - order columns in the seq of the lookup table done

@Hello-bn2yc 4 ай бұрын

I come away from watching your videos feeling very defeated. I would love to do half of what you do.

@mienzillaz 7 ай бұрын

As soon i saw the problem I paused the video and gave it a go. let Source = Excel.CurrentWorkbook(){[Name="Dayz"]}[Content], Initial = Table.AddColumn(Source, "MappedDays", (OT)=> Table.PromoteHeaders(Table.Transpose(Table.SelectColumns(Table.TransformColumns(Table.SelectRows(Letterz, each List.Contains(Text.Split(OT[Days],","),[Letter])), {"Letter", each true}),{"Day","Letter"})))), #"Expanded MappedDays" = Table.ExpandTableColumn(Initial, "MappedDays", Letterz[Day], Letterz[Day]) in #"Expanded MappedDays"

@buchlotnik 7 ай бұрын

let f=(x)=>Excel.CurrentWorkbook(){[Name=x]}[Content], g=(x)=>[a=List.Transform(Text.Split(x{0},","),(x)=>Record.Field(dict,x)), b=Record.FromList({x{0}}&List.Repeat({true},List.Count(a)),{"Days"}&a)][b], b = f("base"), dict = Record.FromList(b[Day],b[Letter]), to = Table.FromRecords(Table.ToList(f("tbl"),g),{"Days"}&b[Day],MissingField.UseNull) in to