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

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

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

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

Amazing example, thanks for bringing this with us.

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

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

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

M-code is magic , need more🎉

Nested knowledge! Superb. Write a book 🚀

Ek Number bhai

Thanks Chandeep, this is excellent.

Beautiful logic applied

Really you Genius at Power Query.. ❤

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!

Great Video , Thanks Chandeep.

Just brilliant !!

Thank you very much. Very interesting.

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?

Awesome. Thankz bro

Great 💯👍

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

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

Super.👍👍

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

What sort of wizardry is this? Incredible

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

You are awesome bro

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

Genius as always. Thanks from South Africa!!

@GoodlyChandeep

7 ай бұрын

Thanks Chris

wonderful

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

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

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

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!

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.

❤❤❤

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 ай бұрын

kzread.info/dash/bejne/Z6dsrdtuopeZpaw.html

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

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.

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

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/

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

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

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"

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