The Magic of Working with Lists in Power Query

[@NoiseBlindness]

Each of your videos is absolute gold.

[@gabrieldiazireland7157]

Great way of making the model resilient to changes.

[@stephenstacey5369]

Thanks!

[@GoodlyChandeep]

Many Thanks Stephen :)

[@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]

Thanks for the tutorials and comments!

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

[@MichaelBrown-lw9kz]

Excellent explanation on how to apply Lists in Power Query formulas.

[@GeertDelmulle]

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]

👍

[@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]

You have stack a simple video with a amazing tricks... Thank you

[@vishaljhaveri7565]

One of the best beginners friendly video on list.

[@GoodlyChandeep]

Glad you think so!

[@OsamaHassanin]

As usual, Awsome. For list of lists we can also do it by List.Zip.

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

[@garciarogerio6327]

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

[@ricardobunge5957]

Bravo! Another fantastic video! Thanks Chandeep - your contributions to the community are very, very much appreciated!

[@GoodlyChandeep]

Glad you like it!

[@gennarocimmino]

Great Chandeep A big thank you for sharing this knowledge with great passion and simple, engaging language.

[@jimfitch]

Excellent technique & tutorial, Chandeep - thank you!

[@GoodlyChandeep]

Glad you like it!

[@iankr]

Many thanks, Chandeep. I learn a lot from your videos.

[@GoodlyChandeep]

Glad they are helpful Ian!

[@brianroberts1363]

Chandeep - This was a brilliant video - you have an amazing way of explaining things and I've learnt a lot from you. Thankyou

[@inascaro]

Execellent explanation! Clear even for a novice like me 🙂 Thank you

[@GoodlyChandeep]

Glad it was helpful Inascaro!

[@KelvinAdityo]

I think I'm getting smarter (at my own pace) by watching your videos. Thanks.

[@SalihKocaSK]

Great job Chandeep. Thanks for your efforts.

[@GoodlyChandeep]

It's my pleasure! 😊

[@LogicQuest]

Plz keep posting regularly. Want more

[@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.

[@elbadlis]

Chandeep, you are awesome. Amazing. Thank you for the lesson.

[@GoodlyChandeep]

My pleasure

[@ahmadrais3585]

Love your practical advice and relevant topics you share about!

[@GoodlyChandeep]

Thank You !

[@gfcsolomon]

You are just awesome! Your videos are fantastic! I just want to say a big THANK YOU.

[@parshuramnangare8577]

Thank you so much Sir for providing such a valuable knowledge.

[@hugomelgar5046]

Chandeep, thank you for sharing your knowledge. Pretty nice video¡

[@debbie6085]

Thank you. That was great. I'm really starting to understand the power of Lists.

[@GoodlyChandeep]

Wonderful!

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

[@Sumanth1601]

Excellent content ☺️👍 very helpful:)

[@GoodlyChandeep]

Glad it was helpful!

[@pierre-louisviala3191]

Thanks Chandeep ! Smart as usual !🙂

[@GoodlyChandeep]

Glad you like it!

[@KuldeepSingh-nq1vi]

Awesome video again, please make other basic videos like this..Such as how to use underscore and each keyword in power query..

[@mecorowa5642]

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"

[@databird5755]

This was amazing. And you explained it very well. Thank you!

[@GoodlyChandeep]

Glad you like it!

[@AlexRuu]

So good explained, I feel like magician 🎩 with Power Query and this M language

[@yookusui9764]

Genius seriously.

[@GoodlyChandeep]

Thank you!

[@komanguy]

Thanks a lot for those powerful tips.

[@GoodlyChandeep]

Glad you like them!

[@karimallahwala7022]

Excellent und very useful content.Thank you so much for sharing your Knowledge

[@GoodlyChandeep]

Glad you liked it!

[@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.

[@FRANKWHITE1996]

Basically essential PQ video. Thanks for sharing.

[@GoodlyChandeep]

Glad you like it!

[@modernboutique1389]

Amazing session,

[@GoodlyChandeep]

Glad you enjoyed it

[@heriniainatza3066]

great tuto. thank you very much.

[@maryjavasilyevna861]

Thank you 🙏🏻 great video

[@IamMiteshParmar]

Thanks for posting useful contents 👍

[@GoodlyChandeep]

Glad you like them!

[@skimpylemon8034]

Fantastic video! Thank Chandeep :)

[@GoodlyChandeep]

Glad you liked it!

[@vacilando86]

Do you have a video covering "each", '_", (), {},[ ], etc. By the way, thank you so much for such a great training

[@pmsocho]

Great video! Thanks!

[@TomaszBI]

Greetings Mr Majcher! Love your content!

[@pmsocho]

@@TomaszBI 👍👍

[@julie_chen]

This is exactly what i was searching.🎉👍🙏👏

[@GoodlyChandeep]

Glad it was helpful !

[@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

[@usedax]

very good, I had already done something like this in a very similar way.

[@Ssrnfamilyvlogs]

How do you filter the data in power bi using parameters.

[@ryoshihiro]

Very useful thanks a lot

[@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

[@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

[@keagankemp6275]

Aah what can I say, if you good you’re good. Thanks for this much needed!

[@GoodlyChandeep]

You’re welcome 😊

[@zakincc]

You are excellent sir

[@lionels839]

Would be great to see the same for formatting numbers and rounding Numbers

[@tomstrack42]

Brilliant, thanks!!!

[@GoodlyChandeep]

You're welcome!

[@kennethstephani692]

Great video!!!

[@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) ?

[@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

[@hariprasad289]

Superb Sir

[@GoodlyChandeep]

Thanks!

[@udayteja6595]

Informative...

[@paulouwerkerk-n1d]

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?

[@峰王]

AMAZING SIR

[@youugoo22]

but thank you.....gives insight in navigating the weeds of power query

[@CAKimberlyLewis]

"SomeData" I can't stop laughing! This video is brilliant for SO many reasons. Bravo!

[@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?

[@CMN1098]

How do I apply this when I’m combining multiple worksheets

[@gxordi]

Thanks.

[@GoodlyChandeep]

Welcome!

[@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.

[@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?

[@SP_Investments]

How to use network day Excel formula in power query, anyone knows

[@sandipansarkar9211]

finished watching

[@kebincui]

Super👍

[@GoodlyChandeep]

Thank you 👍

[@smartwork4768]

thanks dear

[@GoodlyChandeep]

Welcome 😊

[@raimundojs9547]

Excellent! Wouldn't be useful the following function (to avoid the Table.Transpose)? Table.ToRows(table as table) as list

[@vishnupp5944]

Arey,,, fantastic

[@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

[@bannubro1729]

Helpful

[@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.

[@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).

[@AhmedAriem]

it's possible: Table.ReplaceErrorValues(table, List.Transform(Table.ColumnNames(table),(x)=>{x,null}))

[@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

[@sivablazinghawk]

Hi Chandeep, Can you please Share the working file of this video if possible.

[@GoodlyChandeep]

goodly.co.in/magic-working-lists-power-query/

[@EtchTzy]

11:10

[@AshokSankarVarmaChekuri]

❤️

[@GoodlyChandeep]

Thanks!

[@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.

[@KrzysztofPoradziński]

nice witchcraft Sir

[@Casa-gl8gh]

i love u

[@somkanskar8931]

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]

This time you went too far in the second example, with the Table.FromRows function, it is simpler

[@TomaszBI]

Nice, but I was excepting little more "magic" .

[@youugoo22]

too much work....got to be an easier way

[@briandennehy6380]

Lol. Nothing worth doing is easy

[@youugoo22]

almost 15 minutes to show a conversion tech....🤔