Where do you learn this stuff? As usual mind blown.
@joedi
Жыл бұрын
I’ve seen this pattern in Matt Allington’s work but Chandeep has so much more to share. The bro rocks!
@GoodlyChandeep
Жыл бұрын
All thanks to the shitty data that work with. 😀
@wayneedmondson1065
Жыл бұрын
Good one Chandeep. After List.Split, I used Table.FromRows, as in: let Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content], ListBucket = List.Split(Source[Data],4), MakeTable = Table.FromRows(ListBucket,{"Name","City","Age","Phone"}) in MakeTable Just another approach. Thumbs up!!
@user-mma173
Жыл бұрын
Yes, this is better.
@joneskiller8
Жыл бұрын
💛Thank! I was scratching my head why isn't he using a Table.FromRows function, there has to be one!
@GoodlyChandeep
Жыл бұрын
I tried using Table.FromRows but at the time of recording the video it returned some stupid error so I switched to creating a record and then a table. But I agree.. that is shorter approach. Thanks a lot!
@MrAbrandao
2 ай бұрын
once more your work is great.
@saikumarboga3383
Жыл бұрын
Hi, video is mind blowing, suppose if column is sorted like all texts are in upper records and all the numbers are started from end of text values, how to get the sort order which shown in the video from that?
@stretch233
Жыл бұрын
Brilliant as usual! I just had this type of data problem come my way so I’m glad I could use the M code version to solve it, thanks Chandeep!
@willm7994
Жыл бұрын
Dude Thank You!!!!! 😀😃😀 looking for this solution for a while thanks !!!!
@guilouback
Жыл бұрын
man, you make sooooo usable tutorials. thank you!
@GoodlyChandeep
Жыл бұрын
Happy to help Gui!
@anuradhaabewardana314
11 ай бұрын
Hey Chandeep, Is there a way to group this if the number of rows in the bucket vary. Thank you
@shrirajdeshpande8125
Жыл бұрын
Always comes up with super fantastic approaches
@GoodlyChandeep
Жыл бұрын
Thanks Shriraj!
@enricomendiola9952
Жыл бұрын
Hello this is a great solution! I was wondering after converting all of those into individual records. When load it into excel sheet how to load the records only with the column headers values of records only. Thanks in advance
@christophertauss3640
Жыл бұрын
Very cool - both methods, but, of course, I was really impressed with the M code method. Great explanation and a great demo of how M code can easily process lists and tables. M Code, though not initially all that intuitive, is amazing in its elegance and what it can do. Well done, Microsoft and thanks for the great presentation, Goodly. You make me very glad that I am a subscriber.
@johnedwards7024
6 ай бұрын
Chandeep, nice video. What would you do if the number of rows to unstack varied. I have an issue where I am unstacking an address. At time it has the first level and then immediately the city and state. at other times it has the first address, then say a suite number, and then the line with city and state. Therefore, it is at times three lines and at other times only two lines.
@andrewsinha2785
Жыл бұрын
Peace and love to you. Thanks and good luck in your life.
@BhavyaGupta
Жыл бұрын
Sir both the approaches are Awesome 👏 Thanks for making such tutorials 🙂 In the second approach using M, can we directly use Table.FromRows after the split step, giving column names as list in second parameter ??
@GoodlyChandeep
Жыл бұрын
I guess not.. Each list item needs to be separated with a comma to put it out in columns.
@TheMoh148
Жыл бұрын
Yes you can ❤
@BhavyaGupta
Жыл бұрын
@@GoodlyChandeep I mean something like this - let Source = {"A", 25, "Mumbai", "B", 30, "Delhi", "C", 27, "Chennai"}, tbl = Table.FromRows(List.Split(Source,3),{"Name","Age","City"}) in tbl
@TheMoh148
Жыл бұрын
@@BhavyaGuptaGood
@BhavyaGupta
Жыл бұрын
@@TheMoh148 Thank you :)
@TheMoh148
Жыл бұрын
Many thanks Chandeep. You can do it in two steps only List.Split & Table.FromRows Regards Mohammed from Algeria
@ym10up
9 ай бұрын
I do have a question, please. I was able to follow along the first method all the way till the pivot column part. When I clicked on the pivot column button, instead of giving me the table as your example, I got a table where the first batch of rows only contain value in the first column, then the next batch of rows only contain value in the second column, and so on. It appears that the pivot did not reference both the Module and Subtraction as index for columns and rows. My data has a slight difference than yours. Your data was in a single column and you added column headers later on. My data is in two columns, all the headings in one column and all the values in the other. I tried it with the headers column removed to mimic your data and it's worse than keeping the headers column and pivoting it. Could you please give me some pointers? Thank you.
@mostafayeganeh6862
4 ай бұрын
Thanks so much, borther! It is really helpful.
@arne.munther
Жыл бұрын
Would love to see you solve more of these problems. Here I am thinging of the ones in Chapther 13 of the book Master you data.
@danishnawaz7504
Жыл бұрын
Excellent, Plz post maximum videos on Power Query Vidoe..
@JohnAdair-k7j
Жыл бұрын
What column did you pivot on at mm 2:00 (Pivoted Column1)?
@ssurajkr1234
Жыл бұрын
DAX advanced course available? If yes link please
@m.raedallulu4166
Жыл бұрын
Beautifully Professional !
@GoodlyChandeep
Жыл бұрын
Thank you Raed!
@shanshines11
Жыл бұрын
Given we do not have PQ and M code, can we still do this in plain Excel with formula?
@pattyboy1233
Жыл бұрын
kool. Very informative Would this method work with a uneven or random number fields, say a comment field or a 2nd phone number. How would you handle that?
@GoodlyChandeep
Жыл бұрын
Upcoming video 😀
@KuldeepSingh-nq1vi
Жыл бұрын
You are really champ of Power Query❤❤❤
@GoodlyChandeep
Жыл бұрын
Thanks Kuldeep!
@josh_excel
Жыл бұрын
I tried doing something equivalent using some different formulas and I agree this seems to be the most efficient approach. It is possible to include the types in the Table.FromRecords step, but since the data is text, it won't properly convert it, so you'll need a separate step to convert to the correct data types.
Great approach, as usual, but... is it more efficient? Whenever I work with large set of data an use list functions I struggle to output the results!
@GoodlyChandeep
Жыл бұрын
I doubt that lists are slowing down your query. It could be something else. I generally try to avoid pivoting, transposing, sorting, distinct count large tables
@abhijeetshetye3764
Жыл бұрын
Again amazing video sir… thank you 😊
@GoodlyChandeep
Жыл бұрын
Glad you like it Abhijeet!
@amitsawant2577
Жыл бұрын
Hi, please can you share the logic to show the week from Sunday to Saturday. If month ends in between week like on Tuesday or Wednesday, it should still consider the dates from next month till Saturday and consider those dates in previous month's last week
@tecwzrd
Жыл бұрын
Love the Power Query tutorial. Using the TRANSPOSE formula also would have been a quick way to get the vertical data horizontally by counting out the lines in column B and creating an if statement in column C like =IF(B1=1,TRANSPOSE(A1:A4),"") and then filtering out the blank lines. Using Power query is cleaner though :) Love using custom columns. Last time I used Power Query was to duplicate lines based on a header reference as the value using a table within a table Table.Repeat(Table.FromRecords({[Col1=1]}),[#"header_reference"])
@arne.munther
Жыл бұрын
Do you mean Table.Transpose ?
@tecwzrd
Жыл бұрын
@@arne.munther No, just the regular TRANSPOSE excel formula to make an array of data that is vertical to horizontal or vice versa. Sometimes diving into Power Query is more trouble than it's worth and simple regular formulas can do the job much quicker.
@PavanKumar-vi7hd
Жыл бұрын
Great Video Chandeep
@GoodlyChandeep
Жыл бұрын
Thanks Pavan!
@VijayYadav-tx6ft
Ай бұрын
if i want to make list in base of blank row what is necessary changes
@ArthurCapers
Жыл бұрын
HI Goodly, I love the approach, but I would like to use your approach on a huge data set. The loading into power query is the easy bit, but how to transform it into a list when the number of rows per record varies and each rows contains has different number of elements on it. name and id might be on row 1 or split over 2 rows. each record contains about 12 elements but extracting it across possible up to 8 rows. thanks for your insight. the data set is a mainframe extract
@excelworx8712
Жыл бұрын
Hi Chandeep, my query is the same - I have different numbers of rows per record. To make it even worse, my list has an additional column with related information. The first 2 columns have payables from a salary sheet and the next 2 columns have the deductions
@GoodlyChandeep
Жыл бұрын
Send me a sample and expected output - goodly.wordpress@gmail.com
@wensesvincen4877
Жыл бұрын
@@GoodlyChandeep Waiting for this Reply....Can i send you my own dataset too?
@GoodlyChandeep
Жыл бұрын
Yes sir, please! Thanks
@excelworx8712
Жыл бұрын
@@GoodlyChandeep I have done so, thanks in advance!
@PurplePegasus2531
Жыл бұрын
Omg I literally have a project I can use thus solution for right now!!!!
@GoodlyChandeep
Жыл бұрын
Cool!
@piotrsiwiec4396
Жыл бұрын
Great video, nut I have a question, how applied this method to a data where number of information change for every person. For example Cecilia has 3 additional information, Iris has 7, Joanne has 5, Peter has 3 and so on.
@GoodlyChandeep
Жыл бұрын
May be the next video 😁
@megabuilds3007
Жыл бұрын
Great, Thank you.
@007lbp
Жыл бұрын
Fantastic!
@marivicmawanay3246
Жыл бұрын
Can you help how to code dynamically Multiple skills and proficiency pivoted
@sabarirajagopalan9688
Жыл бұрын
You are just awesome 🎉
@rrrraaaacccc80
Жыл бұрын
Excellent 💯👍
@Luciano_mp
Жыл бұрын
Thank you!👍
@Chris_in_fremantle
Жыл бұрын
Very clever.👍
@GoodlyChandeep
Жыл бұрын
Thanks Chrisinfreo!
@choiseunghyunify
5 ай бұрын
What if you have many columns of data on the same spreadsheet?
@sundarun
Жыл бұрын
How would you do if there are 1-M rows under a heading
@michaelt312
Жыл бұрын
May I ask what software you use for drawing on screen?
@GoodlyChandeep
Жыл бұрын
Zoom it
@michaelt312
Жыл бұрын
@@GoodlyChandeep, thank you! And from MS. Awesome. Looks like a great aide for classroom settings when training.
@eslamfahmy87
Жыл бұрын
Shall be make a live steps for interface instead the recorded
@AzharKhan-kd9bf
Жыл бұрын
Hi can any 1 share the M Code so that its easy to copy as noting it down from video causes troubles....
@GoodlyChandeep
Жыл бұрын
You can get the M code from the description's downloadable file.
@ricckymishra
Жыл бұрын
Sequence formula can now make it super quick.
@natatdem
Жыл бұрын
Que hago en este caso: tengo una lista D ,x1,x2 D c1,c2,c3, F d1,d2,d3,d4 y necesito que D,D y F me queden como otra columna al lado de la lista?? 0:09
@natatdem
Жыл бұрын
[D,D],[D,x1] ,[D,x2], [D,D],[D,c1] ,[D,c2], [D,c3], [F,F],[F,d1] ,[F,d2], [F,d3],[F,d4] sería el resultado de la matriz
@victor_wang_1
Жыл бұрын
Afer List.Split, you could have simply used Table.FromRows. No need for records here :).
@GoodlyChandeep
Жыл бұрын
I agree. During recording the video some error cropped so I switched to records. Thanks Victor!
@eslamfahmy87
Жыл бұрын
Actually, I left many of comments to you with appreciation or explanation but unfortunately you did not answer for any!
Пікірлер: 89