that first one contains a solution I couldn't figure out or find anywhere else, your videos are always top quality from every aspect, big thanks, highly appreciated :)
Sensational tutorial, diving deep into M Code Magic and playing arround with lists and tables. Thank you for sharing, you truly master the state art of teaching. Keep it up
Hey, Chandeep, I love your way of teaching. First of all, you explain the logic behind the problem and then you also get fun in fixing the issue. Everything seems so easy 🙂 Your knowledge of the matter is outstanding. Great great job, man!
I really like to know the M language, but you are too fast for me. I am in awe of your mastery of M language. Honestly, there was a time I was stuck in my work, I searched Google and RUclips, I couldn't find any solution except for your video of creating a total row for a table of columns. That was life-saving. Thank you Chandeep
This is an excellent example of utilizing the advanced unpivoting technique. Thank you for the fantastic video and explanation! I’ve attempted to solve these three examples using a different method, which may provide your audience with an alternative solution, as outlined below. Example1: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Custom1 = Table.Combine(List.Transform(Table.Split(Source,3),each Table.PromoteHeaders(Table.Transpose(_)))) in Custom1 Example2: let Source = Excel.CurrentWorkbook(){[Name="Dataset"]}[Content], split = Table.SplitAt(Source,2), Header = List.Transform(Table.ToRows(Table.FillDown(Table.Transpose(split{0}),{"Column1"})),each Text.Combine(_,"|")), AddHeader = Table.RenameColumns(split{1},List.Zip({Table.ColumnNames(split{1}),Header})), Unpivot = Table.UnpivotOtherColumns(AddHeader,{"Bz"},"Header","Value"), SplitColumnbyDelimiter = Table.SplitColumn(Unpivot, "Header", Splitter.SplitTextByDelimiter("|", QuoteStyle.None), {"Company", "Dept"}), ChangedType = Table.TransformColumnTypes(SplitColumnbyDelimiter,{{"Bz", type text}, {"Company", type text}, {"Dept", type text}, {"Value", Int64.Type}}) in ChangedType Example3: let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], Custom1 = Table.Combine(List.Transform(List.Split(Table.ToColumns(Source),3),each [a=Table.FromColumns(_), b=Table.PromoteHeaders(Table.Skip(a,1)), c=Table.AddColumn(b,"Country",each a{0}[Column1]) ][c] )) in Custom1
Oh man, watching you keep adding to the first M code formula instead of using multiple steps reminded of Matt Damon talking about Jack Nicholson in the movie "Departed".... "Now, you COULD end the scene there, but if you keep the camera rolling...." 😁😁😁. You're like the gift that keeps on giving!
Hey Chandeep! In your third example you hardcoded the 3 (25:00). You could wrap the SecRecordAsHeaderList step into List.Count, so you get the number of the cols!
Great video, I know it 's not that practical but for fill up down, I used let f = { Table.FillUp, Table.FillDown} in List.Accumulate ( {0..1}, Table.Transpose(Custom1), (s,c)=> Function.Invoke( f {c} , { s, Table.ColumnNames( s ) } ) ) , oh great use to combine columns.
my brotha Goodly I get this crap all the time....makes me wanna open up 'a can of woop ass' . pisses me off to no end. Clients have no passion for their data. thank you for sharing. Power Query is the magic in the data kitchen. thanks brotha for your insight as always
as an alternative to adding an index try zip; = List.Transform( List.Zip( { ListOfTables, {"Engalnd","India"} } ), (x)=> Table.FromRows( {x} ) ) then table combine .
Remember these all function is little complicated so I hope you will tell us the way by which we can apply logic in real time data problems because every time we have a different situation
Basically instead of using Unpivot table he s using language interesting approach but it s more complex than necessary when you can use more simpler things
hey goodly I’m looking to enroll myself in dax course of yours can you please tell me if there is a doubt session. Can we communicate our doubt to you.
![[#2024MAMA] BIGBANG (빅뱅) - 뱅뱅뱅 (BANG BANG BANG) + FANTASTIC BABY | Mnet 241123 방송](http://i.ytimg.com/vi/EQsYfiPR9uM/mqdefault.jpg)
Download the file ⬇ - goodly.co.in/advanced-unpivoting-tricks-power-query
❤❤
Hey Chandeep
you are teaching like a hurricane.
I really enjoy every second. None can be better for M language, I'm convinced.
that first one contains a solution I couldn't figure out or find anywhere else, your videos are always top quality from every aspect, big thanks, highly appreciated :)
Sensational tutorial, diving deep into M Code Magic and playing arround with lists and tables.
Thank you for sharing, you truly master the state art of teaching. Keep it up
You are the big boss of PQ, respect
Chandeep, you've not only helped learn power query, but you've taught me some really important coding skills too!
Incredible, pure magic {M}.... Masterfully explained .... thanks for sharing!
Love this... you gave me a lot of good ideas to try for myself!! Thanks
Hey, Chandeep, I love your way of teaching.
First of all, you explain the logic behind the problem and then you also get fun in fixing the issue.
Everything seems so easy 🙂
Your knowledge of the matter is outstanding.
Great great job, man!
I really like to know the M language, but you are too fast for me. I am in awe of your mastery of M language. Honestly, there was a time I was stuck in my work, I searched Google and RUclips, I couldn't find any solution except for your video of creating a total row for a table of columns. That was life-saving. Thank you Chandeep
You do know that YT provides you with a way to slow down the videos, right? :-
So amazing Chandeep!!! Thank you
I'm facing a real challenge right now, and this video came at the perfect time. Highly appreciated
This is an excellent example of utilizing the advanced unpivoting technique. Thank you for the fantastic video and explanation! I’ve attempted to solve these three examples using a different method, which may provide your audience with an alternative solution, as outlined below.
Example1:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 = Table.Combine(List.Transform(Table.Split(Source,3),each Table.PromoteHeaders(Table.Transpose(_))))
in
Custom1
Example2:
let
Source = Excel.CurrentWorkbook(){[Name="Dataset"]}[Content],
split = Table.SplitAt(Source,2),
Header = List.Transform(Table.ToRows(Table.FillDown(Table.Transpose(split{0}),{"Column1"})),each Text.Combine(_,"|")),
AddHeader = Table.RenameColumns(split{1},List.Zip({Table.ColumnNames(split{1}),Header})),
Unpivot = Table.UnpivotOtherColumns(AddHeader,{"Bz"},"Header","Value"),
SplitColumnbyDelimiter = Table.SplitColumn(Unpivot, "Header", Splitter.SplitTextByDelimiter("|", QuoteStyle.None), {"Company", "Dept"}),
ChangedType = Table.TransformColumnTypes(SplitColumnbyDelimiter,{{"Bz", type text}, {"Company", type text}, {"Dept", type text}, {"Value", Int64.Type}})
in
ChangedType
Example3:
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
Custom1 = Table.Combine(List.Transform(List.Split(Table.ToColumns(Source),3),each
[a=Table.FromColumns(_),
b=Table.PromoteHeaders(Table.Skip(a,1)),
c=Table.AddColumn(b,"Country",each a{0}[Column1]) ][c]
))
in
Custom1
Oh man, watching you keep adding to the first M code formula instead of using multiple steps reminded of Matt Damon talking about Jack Nicholson in the movie "Departed"....
"Now, you COULD end the scene there, but if you keep the camera rolling...." 😁😁😁.
You're like the gift that keeps on giving!
Wonderful video. I recently struggling with second problem. I got the solution by you. Thanks Chandeep
@GoodlyChandeep is your book going to be available digitally on the Google Books store or do we have to buy a hard copy?
Sir when you are going to lunch your book of M CODE language.
I'm your biggest fan.
Your way of teaching is excellent ❤❤❤
This is magic! So efficient and compact - love it! 👌🙏
Hey Chandeep!
In your third example you hardcoded the 3 (25:00).
You could wrap the SecRecordAsHeaderList step into List.Count, so you get the number of the cols!
Amazing - that logic is awesome. Lists are scary cool
This is brilliant Chandeep. Just what was needed for the problem I sent you. Thank you very much.
Thank you for the detailed video on ower query editor data transformation.
Amazing! you provided the exact solution I needed! thank you so much for all of your help and easy to understand teaching style!!
Great video, I know it 's not that practical but for fill up down, I used
let f = { Table.FillUp, Table.FillDown} in
List.Accumulate ( {0..1}, Table.Transpose(Custom1), (s,c)=>
Function.Invoke( f {c} , { s, Table.ColumnNames( s ) } ) ) , oh great use to combine columns.
Thanks so much Chandeep, these tutorials are fantastic. Keep 'em coming!
Wow, brilliant! Congratulations! Thank you.
It's just amazing tricks, thank you Chandeep for sharing with us.
Stunning Sir. Plz do more videos.
This is incredible. I check out your master m language course it's very expensive. Kindly reduce the cost a lot
😂
🤣 I need a new car .. I´m gonna use similar technique maybe it ´ll work !!!! tks you !!!
Its worth it though.
Thank you for this excellent presentation. It is extremely helpful.
Great tricks. Thanks for sharing
Excellent... thanks for sharing 🎉
Amazing, Thanks a lot
This is awesome
another great video... Thanks...
Another great video!
You make power query seem so damn easy
Whow. Apart from the excellent content - where did you learn to teach so well?
I wonder whether you speak with HUMANS in M language or not... 😁 Simply Superb
Bloody awesome thanks
Awesome! as always.Thank you
Wow incredible❤
Great explanation. Can you show a power BI statement month by month with the months as column headers? It’s typically how data is presented.
You are just Amazing!
Thanks 🙏 eagerly waiting for your video
A masterpiece!
Great tricks 🎉
Fantastic! Awesome!
Nice one
I subscribed because of this video 🎉 keep it up ❤
Good Stuff Goodly!
Hi, in 20:41 you're refering to row as a column;) but I get the idea;)
my brotha Goodly I get this crap all the time....makes me wanna open up 'a can of woop ass' . pisses me off to no end. Clients have no passion for their data. thank you for sharing. Power Query is the magic in the data kitchen. thanks brotha for your insight as always
Just awesome❤❤
Pretty darn awesome 👍👏👏👏
This person is a wizard😅
as an alternative to adding an index try zip;
= List.Transform(
List.Zip( { ListOfTables, {"Engalnd","India"} } ),
(x)=> Table.FromRows( {x} ) )
then table combine .
Remember these all function is little complicated so I hope you will tell us the way by which we can apply logic in real time data problems because every time we have a different situation
Thanks for the great video, Chandeep!
Just one question - Why don't we use Table.Transpose() instead of Table.FromColumns(Table.ToRows()) at 3:40?
I thought transposing 2 rows wouldn't slow down the query.
17:49 Couldn’t have we split the column before unpivoting? Just curious. Thanks!
Thanks brotha
I love his teachings. Can anyone fund his courses for me, please ?
00:24 Example 1
06:50 Example 2
18:28 Example 3
I've noticed that you prefer to put everything into one step. Is it more efficient or something? Why not doing it in separate steps?
Suppose in the table consist few column null value and in excel two table between empty two columns
how can we handle that
in My Power Query Editor Add New Step not showoing helper Selection in excel is there any opetion to activate this like Power Bi?
Basically instead of using Unpivot table
he s using language
interesting approach but it s more complex than necessary when you can use more simpler things
Thanks
Thank you so much!
I don't know why your video is like ASMR to me😅. 30 mins went just like that
Can we not do everything using UI?
How to share powerquery data file or mail to someone, please share
Any discount for your 3 course bundle
If i took the course Mastering Dax, will i get all the video's at a time or its like adding videos weekly?
you'll get all the videos. please drop me a note on chandeep@goodly.co.in
hey goodly I’m looking to enroll myself in dax course of yours can you please tell me if there is a doubt session. Can we communicate our doubt to you.
All students leave comments or post their questions in the community. I hope that helps 😀
Third time of watching, I must get out more. 🙄🙄
Who on hell - a lot 😁
Another awesome video!!