Hey Chandeep, Here are my suggestions for the challenges you brought up: 1. Lists can be down-selected just like tables, here's the formula I tried: • List.Select(Table.ColumnNames(Source), each _="Name" or Text.StartsWith(_,"Col")) • in hind sight: this is exactly the formula Wayne suggested below. 🙂 2. Here's another way to remove the errors, using some more List functions: • AddNull = Table.AddColumn(ConvertNamesToTable, "Null", each null), • ZipNull = List.Zip(Table.ToColumns(AddNull)), • ReplaceErrors = Table.ReplaceErrorValues(FilterColumns, ZipNull)
@udayteja6595
5 ай бұрын
👍
@wayneedmondson1065
Жыл бұрын
Thanks Chandeep. Another aweseome lesson and video! For those who like to write a little M, you can get the ColList in one step with: = List.Select(Table.ColumnNames(Source), each _ = "Name" or Text.StartsWith(_, "Col")) and the RenameCols in one step with: = Table.ToColumns(Table.Transpose(Table.AddColumn(Table.FromList(ColList), "Column2", each null))). Always fun to learn and grow skills at your channel. Thumbs up!
@julie_chen
Жыл бұрын
👍
@jimfitch
Жыл бұрын
Thanks, Chandeep & Wayne!
@Terraque
6 ай бұрын
Thanks for the tutorials and comments!
@gabrieldiazireland7157
Жыл бұрын
Great way of making the model resilient to changes.
@martyc5674
Жыл бұрын
This is such a brilliant video- I’ve worked a bit with M a bit and there are some key points in here that are really well explained. (The UI being greyed out for lists, tabletocolumns etc!!) This video is going to the top of my favorites!!
@GoodlyChandeep
Жыл бұрын
Glad you enjoyed it!
@gfcsolomon
12 күн бұрын
You are just awesome! Your videos are fantastic! I just want to say a big THANK YOU.
@garciarogerio6327
2 ай бұрын
Taking Power Query to the next level. Edit (a day later): A dashboard I published on the PowerBi service failed to update. I verified that one of these random columns (Column14) is missing in 3 tables (it has probably been filled in as there are changes in the Excel tables. I prefer to get data from tables to prevent this, but the information owner doesn't want to have tables. With this video, the problem is dynamically solved. Thank you again for the tips here and on LinkedIn
@MichaelBrown-lw9kz
Жыл бұрын
Excellent explanation on how to apply Lists in Power Query formulas.
@DineshKumar-xl2ug
Жыл бұрын
You are one of the cleanest presenters in the business. Really love the way your put things together.. following all your DAX vedios. they are awesome..
@power_given
7 ай бұрын
You have stack a simple video with a amazing tricks... Thank you
@ricardobunge5957
Жыл бұрын
Bravo! Another fantastic video! Thanks Chandeep - your contributions to the community are very, very much appreciated!
@GoodlyChandeep
Жыл бұрын
Glad you like it!
@LogicQuest
Жыл бұрын
Plz keep posting regularly. Want more
@brianroberts1363
4 ай бұрын
Chandeep - This was a brilliant video - you have an amazing way of explaining things and I've learnt a lot from you. Thankyou
@christophertauss3640
Жыл бұрын
Exceptionally good explanation. I work with Power Query a lot and marvel at what it can do. M is really a list and table processing language. This video is very crucial to understand M which is, IMO, an elegant but complex language. I have subscribed and plan to listen to your other videos - esp. in Power BI and DAX which I am much less experienced with. Great job!
@heriniainatza3066
Ай бұрын
great tuto. thank you very much.
@OsamaHassanin
Жыл бұрын
As usual, Awsome. For list of lists we can also do it by List.Zip.
@yookusui9764
Жыл бұрын
Genius seriously.
@GoodlyChandeep
Жыл бұрын
Thank you!
@gennarocimmino
Жыл бұрын
Great Chandeep A big thank you for sharing this knowledge with great passion and simple, engaging language.
@KelvinAdityo
9 ай бұрын
I think I'm getting smarter (at my own pace) by watching your videos. Thanks.
@pmsocho
6 ай бұрын
Great video! Thanks!
@TomaszBI
6 ай бұрын
Greetings Mr Majcher! Love your content!
@pmsocho
6 ай бұрын
@@TomaszBI 👍👍
@vishaljhaveri7565
Жыл бұрын
One of the best beginners friendly video on list.
@GoodlyChandeep
Жыл бұрын
Glad you think so!
@ahmadrais3585
Жыл бұрын
Love your practical advice and relevant topics you share about!
@GoodlyChandeep
Жыл бұрын
Thank You !
@KuldeepSingh-nq1vi
Жыл бұрын
Awesome video again, please make other basic videos like this..Such as how to use underscore and each keyword in power query..
@zahoorsarbandi2982
Жыл бұрын
Zabar10.. One of the most important videos on the objects of power query. Kindly make some more videos like this on the basic objects and fundamentals of power query.
@usedax
Жыл бұрын
very good, I had already done something like this in a very similar way.
Thank you. That was great. I'm really starting to understand the power of Lists.
@GoodlyChandeep
Жыл бұрын
Wonderful!
@AlexRuu
Жыл бұрын
So good explained, I feel like magician 🎩 with Power Query and this M language
@inascaro
Жыл бұрын
Execellent explanation! Clear even for a novice like me 🙂 Thank you
@GoodlyChandeep
Жыл бұрын
Glad it was helpful Inascaro!
@vacilando86
Жыл бұрын
Do you have a video covering "each", '_", (), {},[ ], etc. By the way, thank you so much for such a great training
@parshuramnangare8577
Жыл бұрын
Thank you so much Sir for providing such a valuable knowledge.
@iankr
Жыл бұрын
Many thanks, Chandeep. I learn a lot from your videos.
@GoodlyChandeep
Жыл бұрын
Glad they are helpful Ian!
@iantotheh
Жыл бұрын
You indirectly answered a question I had been wondering about: once you create a list how do you get back to the original table? Simply click on Source again! Thank you.
@stephenstacey5369
10 ай бұрын
Thanks!
@GoodlyChandeep
10 ай бұрын
Many Thanks Stephen :)
@txreal2
Жыл бұрын
I usually copy, transpose & select what columns I need in Excel; since my tables have 35 columns to mess with scrolling around. Is it better OR NOT to import that list into PQ & work with it? Great video! Thanks.
@modernboutique1389
Жыл бұрын
Amazing session,
@GoodlyChandeep
Жыл бұрын
Glad you enjoyed it
@elbadlis
Жыл бұрын
Chandeep, you are awesome. Amazing. Thank you for the lesson.
@GoodlyChandeep
Жыл бұрын
My pleasure
@databird5755
Жыл бұрын
This was amazing. And you explained it very well. Thank you!
@GoodlyChandeep
Жыл бұрын
Glad you like it!
@hugomelgar5046
Жыл бұрын
Chandeep, thank you for sharing your knowledge. Pretty nice video¡
@Sumanth1601
Жыл бұрын
Excellent content ☺️👍 very helpful:)
@GoodlyChandeep
Жыл бұрын
Glad it was helpful!
@FRANKWHITE1996
Жыл бұрын
Basically essential PQ video. Thanks for sharing.
@GoodlyChandeep
Жыл бұрын
Glad you like it!
@mecorowa5642
8 ай бұрын
Hi Chandeep, your YT are the best to get in to Power Query. I played a bit around with list and found out it is not necessary to transform the list into table. There are enough list operations to solve it as list. Here my m code: List.Select(Table.ColumnNames (Table.Combine ( Custom4_PromoteHeaders [Custom])), each not Text.Contains (_,"Col")) In my case a was chasing all columns without "Col"
@zakincc
4 ай бұрын
You are excellent sir
@SalihKocaSK
Жыл бұрын
Great job Chandeep. Thanks for your efforts.
@GoodlyChandeep
Жыл бұрын
It's my pleasure! 😊
@karimallahwala7022
Жыл бұрын
Excellent und very useful content.Thank you so much for sharing your Knowledge
@GoodlyChandeep
Жыл бұрын
Glad you liked it!
@youugoo22
11 ай бұрын
but thank you.....gives insight in navigating the weeds of power query
@aman_mashetty5185
Жыл бұрын
i'm glad that i followed your channel.. as always awesome video, Once again thanks for the video
@GoodlyChandeep
Жыл бұрын
Glad to hear that!
@ramanaidug
Жыл бұрын
Thank you so much for the explanation, Can you please let me how to give list for slipting data by position dynamically, Can you please let me is it possible or not
@keagankemp6275
Жыл бұрын
Aah what can I say, if you good you’re good. Thanks for this much needed!
@GoodlyChandeep
Жыл бұрын
You’re welcome 😊
@komanguy
Жыл бұрын
Thanks a lot for those powerful tips.
@GoodlyChandeep
Жыл бұрын
Glad you like them!
@udayteja6595
5 ай бұрын
Informative...
@CAKimberlyLewis
9 ай бұрын
"SomeData" I can't stop laughing! This video is brilliant for SO many reasons. Bravo!
@pierre-louisviala3191
Жыл бұрын
Thanks Chandeep ! Smart as usual !🙂
@GoodlyChandeep
Жыл бұрын
Glad you like it!
@thomaseckersley5026
Жыл бұрын
I'm trying to apply a List of a list for dynamic Col. Names when my table of data has the first row as the year "2023", the second row as the month "August", and the third row as the Day "1" etc. Am I on the right track? Thanks
@Mixolydian74
Жыл бұрын
Just started learning power query and you've been a big help so far! I did have a question regarding the dynamic reordering of columns that I'm hoping you could help with. For instance, what If most of the column header names are years, and I want to reorder the columns so that Latest year and it's corresponding data is on the furthest right column. I believe I need to change the format of the column names from "text" to "date", in order to dynamically reorder them in this fashion but I'm not sure how to achieve this. I want the 1st column in the set of data to stay where it regardless (it's just a string of words, not a year) of any changes to the source data overtime. For some context, the source data that I'm using is getting pulled from the web (to analyze company financial statements) which is why I want to dynamically update the formatting and column order each time the data refreshes. The column names are years, but the formatting on the data under the years varies by each row (i.e., row 3, 10, 17 is in Percentage and rows 6, 11, and 15 are in Number format). However, when the query loads the data into excel, the formatting for everything defaults to "General" and then I have to manually adjust the formatting to perform any excel calculations and further analysis on the data set pulled in. I look forward to hearing from! Thanks, Mike
@maryjavasilyevna861
Жыл бұрын
Thank you 🙏🏻 great video
@julie_chen
Жыл бұрын
This is exactly what i was searching.🎉👍🙏👏
@GoodlyChandeep
Жыл бұрын
Glad it was helpful !
@ryoshihiro
8 ай бұрын
Very useful thanks a lot
@hariprasad289
Жыл бұрын
Superb Sir
@GoodlyChandeep
Жыл бұрын
Thanks!
@sandipansarkar9211
9 ай бұрын
finished watching
@IamMiteshParmar
Жыл бұрын
Thanks for posting useful contents 👍
@GoodlyChandeep
Жыл бұрын
Glad you like them!
@lionels839
Жыл бұрын
Would be great to see the same for formatting numbers and rounding Numbers
@skimpylemon8034
Жыл бұрын
Fantastic video! Thank Chandeep :)
@GoodlyChandeep
Жыл бұрын
Glad you liked it!
@nunolourenco1532
Жыл бұрын
Great as usual but, what if you need FUNCTIONS inside the list items (like in a GROUP BY operation) ?
How do you filter the data in power bi using parameters.
@kennethstephani692
Жыл бұрын
Great video!!!
@GoodlyChandeep
Жыл бұрын
Glad you liked it!
@raimundojs9547
Жыл бұрын
Excellent! Wouldn't be useful the following function (to avoid the Table.Transpose)? Table.ToRows(table as table) as list
@smartwork4768
Жыл бұрын
thanks dear
@GoodlyChandeep
Жыл бұрын
Welcome 😊
@newplayer9503
Жыл бұрын
Hi Chandeep fantasic presentations of solving Dataproblems with using Power Query. I have a lot of learn to use the different functions. Your videos are great. I hoped to solve my problem but I failed. My case is , I get Orders as PDF , the tables inside are not usable, therefore I catch the Pages. But every page had different amount of columns and no possibility headers .I try it with max columnscount to select all columns or DemoteHeaders, then I would combine all columns in a new with delimiter “ “. Result is one Colum and all lines of the pdf If I changed the Pdf , sometimes I have no blanks between the words or not all columns were extract Question : is there a possibility to import more pdf-pages line by line without to much steps in M-Code or how ever ? Do you have a Tipp?
@CMN1098
Жыл бұрын
How do I apply this when I’m combining multiple worksheets
@峰王
Жыл бұрын
AMAZING SIR
@suryatripathi3246
Жыл бұрын
I have applied filter function to certain columns to extract some data which I need to append to other data and sort. But when I import both these columns into power query it shows error and null in cells with filter function and spilled values. How to get around this?
@bharathramc.n7796
Жыл бұрын
Hi Great video Can you please do a similar video where the column names are not bit similar (i e Col1,Col2, Col3,....) as you had shown The user is given the choice to select the required col name/s ( No. of Column are 25).
@user-gb7ec5mj3q
8 ай бұрын
Hi Chandeep, Thanks for all the video's. As a beginner, it is still a bit confusing for me. I try to make a report for a webshop so the third column is used. I have a Table in Excel with two columns, and tried to make a third column that combines the previous text values until a new Type starts. I hoped it was a standard example in any video. Type Text Value New Text Value A 10 10 A 20 10|20 A 10|20 A 40 10|20|40 B 30 30 B 40 30|40 B 30|40 B 50 30|40|50 C C 20 20 C 30 20|30 Do you think it is a standard problem or do I not recognise the solution in video's?
@kunzhang7506
Жыл бұрын
In my Power query (Excel 2019), i do not have the preview of formulas when I creat customer columns. Can you please advise how to resolve? Thks
@vadnerepravin
Жыл бұрын
Thank you sir. I have one query regarding this I have many coulmn Net 5%, Net 12% .... and CGST 5% CGST 12% ... I have another table which we define Net 5% is Net , Net 12% is Net, CGST 5% is CGST so on and so forth Can we combine all Sales Data into user defined named as I mentioned above? Please help me, thank you very much
@bannubro1729
Жыл бұрын
Helpful
@SP_Investments
Жыл бұрын
How to use network day Excel formula in power query, anyone knows
@ManishKumar-eu1qw
Жыл бұрын
How to filter Analytics data in Power bi with parameters?
@ryanyou1583
Жыл бұрын
Hi Goodly, I wonder if you could suggest which book shall I read if want to dive in more to learn M. Thanks😚
@GoodlyChandeep
Жыл бұрын
Official documentation by Microsoft and M primer series from Ben Gribaudo
@ryanyou1583
Жыл бұрын
@@GoodlyChandeep Thank you Sir
@kebincui
Жыл бұрын
Super👍
@GoodlyChandeep
Жыл бұрын
Thank you 👍
@inkuban
Жыл бұрын
Awesome tips for working with lists. The problem with these solutions is that you are entirely dependent on your knowledge base of return types of methods and properties to plugin the parameters within the functions to meet and get the desired results and it will take a while for anyone to bank all that knowledge to be able to apply them and make use of them. Awful design.
@sakibzamanmohammad8895
Жыл бұрын
Hi, could you please help me with last six months sales dax formula by month's slicer selection for excel not for powerbi.
@ajisharavind6937
Жыл бұрын
Use pivot table and use month/ year in slicer
@sakibzamanmohammad8895
Жыл бұрын
@@ajisharavind6937 basically I need rolling distribution (distinct count) for last 6 months or 3 months by slicer selection.
@EtchTzy
3 ай бұрын
11:10
@sivablazinghawk
Жыл бұрын
Hi Chandeep, Can you please Share the working file of this video if possible.
@GoodlyChandeep
Жыл бұрын
goodly.co.in/magic-working-lists-power-query/
@user-rx1si7zr1j
5 ай бұрын
nice witchcraft Sir
@AshokSankarVarmaChekuri
Жыл бұрын
❤️
@GoodlyChandeep
Жыл бұрын
Thanks!
@Casa-gl8gh
Жыл бұрын
i love u
@somkanskar8931
4 ай бұрын
Bhaiya me bhaiya chandeep bhaiya
@carlocalingasan998
Жыл бұрын
I'm all about the lists, 'bout the lists, not table.
@antoines.7682
Жыл бұрын
This about only Dax and Mcodes😢
@cesarsaldana3429
4 ай бұрын
This time you went too far in the second example, with the Table.FromRows function, it is simpler
Пікірлер: 144