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!!!
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!
The trick to doing it in the interface is adding index columns to both tables for a “key”. Duplicate “days”, split to rows, merge the lookup, add a “True” column. Sort, remove columns, pivot, remove index
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?
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
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/
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!
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 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.
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
Hi Chandeep, Can you assist me with how to fetch historical data for NSE stocks from the Equity Pandit website using Power Query, with date references from cells? We need a video demonstrating this process for educational purposes. Please help us understand how to fetch historical data for multiple NSE stocks with date cell references using Power Query from the Equity Pandit website🙏🙏🙏
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.
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
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
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
This is magic. Not only is the build process magical, but the clear explanation tying it all together.
You are a PQ genius, you really are. Thanks Chandeep!
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!!!
986 likes, very underrated video. fantastic work Chandeep
What a guy. Man I appreciate you and these videos.
Amazing example, thanks for bringing this with us.
Essa com certeza é uma daquelas aulas que tenho que ver várias vezes.
Big thumbs up! Great solution, great explanation! 👍👍
Excellent. Ingenious. Easy to follow. It is so much better to learn from examples is a problem --> solution format.
Thanks Chandeep for breaking down your problem solving approach and steps. Indded, sharing your logic provided even more value than the solution.
Nested knowledge! Superb. Write a book 🚀
Genius as always. Thanks from South Africa!!
Thanks Chris
Really you Genius at Power Query.. ❤
Another great video. Gets me reconsidering how I handled some steps in the Call Center dashboard I just finished.
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!
The trick to doing it in the interface is adding index columns to both tables for a “key”. Duplicate “days”, split to rows, merge the lookup, add a “True” column. Sort, remove columns, pivot, remove index
M-code is magic , need more🎉
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?
Genious, I have been struggling to generate multiple columns in a single step, turns out I need to follow the records approach. Thanks
Hello vibhor I think i need your assistance in power query
Thanks Chandeep, this is excellent.
Great Video , Thanks Chandeep.
Thank you very much. Very interesting.
Beautiful logic applied
Ok, big Boss!
Next time i wanna see how you move that tables on the screen with your fingers!🤗
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
Thank you for another great video and if possible please make videos on power automate.
Just brilliant !!
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/
Ek Number bhai
Great 💯👍
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!
Sure!
Nice one, Sharing your logic provides huge value. Any black Friday sales on the courses?🙂
Brilliant as usual. Can you suggest where this solution can be used for any other problem?
What sort of wizardry is this? Incredible
Awesome. Thankz bro
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"} )
I have this same problem have you got the solution
@@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.
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
Super.👍👍
Ah, it's very easy!😂Great job! Thanks for your videos! However, there are no shortcuts. We have to study and try and try again.
You are awesome bro
Nice thanks for that.... could you plz do same this in SQL end
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???
ua-cam.com/video/5s8Ky5r43uI/v-deo.html
Please make short video on PQ editor formating...always confused in brackets
Hi Chandeep,
Can you assist me with how to fetch historical data for NSE stocks from the Equity Pandit website using Power Query, with date references from cells? We need a video demonstrating this process for educational purposes. Please help us understand how to fetch historical data for multiple NSE stocks with date cell references using Power Query from the Equity Pandit website🙏🙏🙏
wonderful
You can share me how to do video this style, please.
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.
Aap live hindi me class lete ho ky sir
www.youtube.com/@desigoodly
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
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
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