The Magic of Working with Lists in Power Query

Подписаться 89 тыс.

Просмотров 80 тыс.

50% 1

Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
In this video, you'll learn about
-What is a List
-Identifying Lists
-Extract & creating a List
-Feeding or Plugging in a List
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨‍🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File - goodly.co.in/magic-working-li...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== CHAPTERS =====
0:00 Intro
0:32 What is a List?
1:44 Explanation of Example 1
3:09 Example 1 - Working with the Column Headers of a Table
6:49 Explanation of Example 2
8:49 Example 2 - Removing Errors from a Table
12:50 3 Key Learnings
13:43 My Courses
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!
- - - - -
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
- Source: goo.gl/fh3rEJ
Official After The Fall RU-vid Channel Below
ru-vid.com/show-UCGQE...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses

Наука

Опубликовано:

29 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 141

@garciarogerio6327 6 дней назад

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

@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 3 месяца назад

👍

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

@NoiseBlindness 11 месяцев назад

Each of your videos is absolute gold.

@gabrieldiazireland7157 Год назад

Great way of making the model resilient to changes.

@power_given 4 месяца назад

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

@MichaelBrown-lw9kz Год назад

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

@ricardobunge5957 Год назад

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

@GoodlyChandeep Год назад

Glad you like it!

@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 3 месяца назад

Thanks for the tutorials and comments!

@ahmadrais3585 Год назад

Love your practical advice and relevant topics you share about!

@GoodlyChandeep Год назад

Thank You !

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

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

@gennarocimmino Год назад

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

@parshuramnangare8577 Год назад

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

@KuldeepSingh-nq1vi Год назад

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

@brianroberts1363 Месяц назад

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

@debbie6085 Год назад

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

@GoodlyChandeep Год назад

Wonderful!

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

@databird5755 Год назад

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

@GoodlyChandeep Год назад

Glad you like it!

@OsamaHassanin Год назад

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

@LogicQuest Год назад

Plz keep posting regularly. Want more

@hugomelgar5046 Год назад

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

@jimfitch Год назад

Excellent technique & tutorial, Chandeep - thank you!

@GoodlyChandeep Год назад

Glad you like it!

@KelvinAdityo 7 месяцев назад

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

@AlexRuu Год назад

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

@maryjavasilyevna861 10 месяцев назад

Thank you 🙏🏻 great video

@vishaljhaveri7565 Год назад

One of the best beginners friendly video on list.

@GoodlyChandeep Год назад

Glad you think so!

@SalihKocaSK Год назад

Great job Chandeep. Thanks for your efforts.

@GoodlyChandeep Год назад

It's my pleasure! 😊

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

@inascaro Год назад

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

@GoodlyChandeep Год назад

Glad it was helpful Inascaro!

@iankr Год назад

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

@GoodlyChandeep Год назад

Glad they are helpful Ian!

@karimallahwala7022 Год назад

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

@GoodlyChandeep Год назад

Glad you liked it!

@FRANKWHITE1996 Год назад

Basically essential PQ video. Thanks for sharing.

@GoodlyChandeep Год назад

Glad you like it!

@julie_chen Год назад

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

@GoodlyChandeep Год назад

Glad it was helpful !

@yookusui9764 Год назад

Genius seriously.

@GoodlyChandeep Год назад

Thank you!

@elbadlis Год назад

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

@GoodlyChandeep Год назад

My pleasure

@komanguy Год назад

Thanks a lot for those powerful tips.

@GoodlyChandeep Год назад

Glad you like them!

@ryoshihiro 5 месяцев назад

Very useful thanks a lot

@pmsocho 3 месяца назад

Great video! Thanks!

@TomaszBI 3 месяца назад

Greetings Mr Majcher! Love your content!

@pmsocho 3 месяца назад

@@TomaszBI 👍👍

@skimpylemon8034 Год назад

Fantastic video! Thank Chandeep :)

@GoodlyChandeep Год назад

Glad you liked it!

@pierre-louisviala3191 Год назад

Thanks Chandeep ! Smart as usual !🙂

@GoodlyChandeep Год назад

Glad you like it!

@usedax 11 месяцев назад

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

@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

@IamMiteshParmar Год назад

Thanks for posting useful contents 👍

@GoodlyChandeep Год назад

Glad you like them!

@tomstrack42 Год назад

Brilliant, thanks!!!

@GoodlyChandeep Год назад

You're welcome!

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

@vacilando86 Год назад

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

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

@zakincc 2 месяца назад

You are excellent sir

@lionels839 Год назад

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

@modernboutique1389 Год назад

Amazing session,

@GoodlyChandeep Год назад

Glad you enjoyed it

@udayteja6595 3 месяца назад

Informative...

@kennethstephani692 Год назад

Great video!!!

@GoodlyChandeep Год назад

Glad you liked it!

@user-jp3kg5ue5t 9 месяцев назад

AMAZING SIR

@CAKimberlyLewis 7 месяцев назад

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

@keagankemp6275 Год назад

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

@GoodlyChandeep Год назад

You’re welcome 😊

@stephenstacey5369 8 месяцев назад

Thanks!

@GoodlyChandeep 8 месяцев назад

Many Thanks Stephen :)

@nunolourenco1532 Год назад

Great as usual but, what if you need FUNCTIONS inside the list items (like in a GROUP BY operation) ?

@bannubro1729 10 месяцев назад

Helpful

@youugoo22 9 месяцев назад

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

@vishnupp5944 2 месяца назад

Arey,,, fantastic

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

@gxordi Год назад

Thanks.

@GoodlyChandeep Год назад

Welcome!

@ramanaidug 10 месяцев назад

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

@hariprasad289 Год назад

Superb Sir

@GoodlyChandeep Год назад

Thanks!

@sandipansarkar9211 6 месяцев назад

finished watching

@smartwork4768 Год назад

thanks dear

@GoodlyChandeep Год назад

Welcome 😊

@thomaseckersley5026 10 месяцев назад

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

@raimundojs9547 Год назад

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

@kebincui Год назад

Super👍

@GoodlyChandeep Год назад

Thank you 👍

@newplayer9503 11 месяцев назад

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?

@Ssrnfamilyvlogs Год назад

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

@AhmedAriem Год назад

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

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

@user-gb7ec5mj3q 5 месяцев назад

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

@mecorowa5642 6 месяцев назад

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"

@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

@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

@SP_Investments Год назад

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

@CMN1098 11 месяцев назад

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

@AshokSankarVarmaChekuri Год назад

❤️

@GoodlyChandeep Год назад

Thanks!

@ManishKumar-eu1qw Год назад

How to filter Analytics data in Power bi with parameters?

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

@Casa-gl8gh Год назад

i love u

@EtchTzy Месяц назад

11:10

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