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)
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!
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!!
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..
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
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!
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.
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.
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"
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
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
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
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?
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?
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?
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.
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
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).
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.
Each of your videos is absolute gold.
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)
👍
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!
👍
Thanks, Chandeep & Wayne!
Thanks for the tutorials and comments!
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!!
Glad you enjoyed it!
Great way of making the model resilient to changes.
Excellent explanation on how to apply Lists in Power Query formulas.
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..
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
You have stack a simple video with a amazing tricks... Thank you
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!
As usual, Awsome.
For list of lists we can also do it by List.Zip.
One of the best beginners friendly video on list.
Glad you think so!
Plz keep posting regularly.
Want more
Bravo! Another fantastic video! Thanks Chandeep - your contributions to the community are very, very much appreciated!
Glad you like it!
I think I'm getting smarter (at my own pace) by watching your videos. Thanks.
Great Chandeep
A big thank you for sharing this knowledge with great passion and simple, engaging language.
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.
Chandeep - This was a brilliant video - you have an amazing way of explaining things and I've learnt a lot from you. Thankyou
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.
You are just awesome! Your videos are fantastic! I just want to say a big THANK YOU.
So good explained, I feel like magician 🎩 with Power Query and this M language
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"
Thank you so much Sir for providing such a valuable knowledge.
Many thanks, Chandeep. I learn a lot from your videos.
Glad they are helpful Ian!
Excellent technique & tutorial, Chandeep - thank you!
Glad you like it!
Do you have a video covering "each", '_", (), {},[ ], etc.
By the way, thank you so much for such a great training
Execellent explanation! Clear even for a novice like me 🙂 Thank you
Glad it was helpful Inascaro!
Genius seriously.
Thank you!
Chandeep, you are awesome. Amazing. Thank you for the lesson.
My pleasure
Love your practical advice and relevant topics you share about!
Thank You !
Awesome video again, please make other basic videos like this..Such as how to use underscore and each keyword in power query..
Chandeep, thank you for sharing your knowledge. Pretty nice video¡
Thank you. That was great. I'm really starting to understand the power of Lists.
Wonderful!
Great job Chandeep. Thanks for your efforts.
It's my pleasure! 😊
Basically essential PQ video. Thanks for sharing.
Glad you like it!
Thanks Chandeep ! Smart as usual !🙂
Glad you like it!
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
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
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
Thank you 🙏🏻 great video
This was amazing. And you explained it very well. Thank you!
Glad you like it!
great tuto. thank you very much.
very good, I had already done something like this in a very similar way.
Excellent content ☺️👍 very helpful:)
Glad it was helpful!
Excellent und very useful content.Thank you so much for sharing your Knowledge
Glad you liked it!
i'm glad that i followed your channel.. as always awesome video, Once again thanks for the video
Glad to hear that!
You are excellent sir
Thanks a lot for those powerful tips.
Glad you like them!
How do you filter the data in power bi using parameters.
Great as usual but, what if you need FUNCTIONS inside the list items (like in a GROUP BY operation) ?
Great video! Thanks!
Greetings Mr Majcher! Love your content!
@@TomaszBI 👍👍
Amazing session,
Glad you enjoyed it
Very useful thanks a lot
Thanks for posting useful contents 👍
Glad you like them!
This is exactly what i was searching.🎉👍🙏👏
Glad it was helpful !
How do I apply this when I’m combining multiple worksheets
Would be great to see the same for formatting numbers and rounding Numbers
Fantastic video! Thank Chandeep :)
Glad you liked it!
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?
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?
Hi Goodly, I wonder if you could suggest which book shall I read if want to dive in more to learn M. Thanks😚
Official documentation by Microsoft and M primer series from Ben Gribaudo
@@GoodlyChandeep Thank you Sir
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
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?
Excellent! Wouldn't be useful the following function (to avoid the Table.Transpose)?
Table.ToRows(table as table) as list
"SomeData" I can't stop laughing! This video is brilliant for SO many reasons. Bravo!
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.
but thank you.....gives insight in navigating the weeds of power query
Aah what can I say, if you good you’re good. Thanks for this much needed!
You’re welcome 😊
Superb Sir
Thanks!
finished watching
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
Brilliant, thanks!!!
You're welcome!
Informative...
Great video!!!
Glad you liked it!
Thanks!
Many Thanks Stephen :)
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).
AMAZING SIR
How to filter Analytics data in Power bi with parameters?
it's possible:
Table.ReplaceErrorValues(table, List.Transform(Table.ColumnNames(table),(x)=>{x,null}))
How to use network day Excel formula in power query, anyone knows
Hi, could you please help me with last six months sales dax formula by month's slicer selection for excel not for powerbi.
Use pivot table and use month/ year in slicer
@@ajisharavind6937 basically I need rolling distribution (distinct count) for last 6 months or 3 months by slicer selection.
Arey,,, fantastic
thanks dear
Welcome 😊
Thanks.
Welcome!
Helpful
Hi Chandeep, Can you please Share the working file of this video if possible.
goodly.co.in/magic-working-lists-power-query/
Super👍
Thank you 👍
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.
11:10
❤️
Thanks!
nice witchcraft Sir
Bhaiya me bhaiya chandeep bhaiya
This about only Dax and Mcodes😢
i love u
I'm all about the lists, 'bout the lists, not table.
This time you went too far in the second example, with the Table.FromRows function, it is simpler
too much work....got to be an easier way
Lol. Nothing worth doing is easy
Nice, but I was excepting little more "magic" .
almost 15 minutes to show a conversion tech....🤔