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

Подписаться 89 тыс.

Просмотров 19 тыс.

50% 1

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!

Наука

Опубликовано:

27 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 51

@tinhoyhu 7 месяцев назад

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

@chrism9037 7 месяцев назад

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

@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!!!

@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.

@efficiency365 7 месяцев назад

Nested knowledge! Superb. Write a book 🚀

@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!

@jerrydellasala7643 7 месяцев назад

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

@davidmaldonadocastillejos3179 7 месяцев назад

Beautiful logic applied

@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

@meganathanve444 7 месяцев назад

Thanks Chandeep, this is excellent.

@KuldeepSingh-nq1vi 7 месяцев назад

Really you Genius at Power Query.. ❤

@SamehRSameh 7 месяцев назад

M-code is magic , need more🎉

@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?

@user-rp1ub1qw6m 2 месяца назад

Great Video , Thanks Chandeep.

@_SachinKhanna 7 месяцев назад

Ek Number bhai

@patrickharilantoraherinjat2994 3 месяца назад

Awesome. Thankz bro

@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

@jianlinchen7978 2 месяца назад

wonderful

@rrrraaaacccc80 4 месяца назад

Great 💯👍

@ExcelWithChris 7 месяцев назад

Genius as always. Thanks from South Africa!!

@GoodlyChandeep 7 месяцев назад

Thanks Chris

@ericrobbins6953 7 месяцев назад

What sort of wizardry is this? Incredible

@nitheshn7019 7 месяцев назад

You are awesome bro

@Rice0987 7 месяцев назад

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

@kebincui 7 месяцев назад

Super.👍👍

@andreascharalambides4412 7 месяцев назад

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

@junaidshaiks1296 7 месяцев назад

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

@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

@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/

@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!

@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

@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 6 месяцев назад

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 6 месяцев назад

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5s8Ky5r43uI.html

@stefankirst3234 6 месяцев назад

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

@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