Absolutely amazing Chandeep, I must admit I used the split column by delimiter into rows way and used a grouping to get the row counts to divide by...so column names are hard coded and not dynamic but you only need to add a step for a new column and the rest works so almost there!! You are a total guru and love your work!
Hi Chandeep Would you also please group them as basic, intermediate and advanced powe query groups and put them as play lists. This will narrow down the search by levels and topic. Love your instant responses. We need more Mcode videos and examples.
mma173 👏 Awesome solution and many thanks Chandeep for demo! I've been looking / waiting for instruction such as this. With this advanced M guidance, I'm confident I'll be able to make transformations I've been dreaming of and really utilize the MS M language doc., both areas where I've been "stuck" and have hindered my growth in PQ skill. Love the List work and dynamic solutions!!
I always come back to this amazing Video. It is really a masterpiece, which shows the great opportunities of Power Query. This Video is a treasure of Knowledge. I am so thankful for this outstanding video.
That's Awesome 👍 I think this is the best channel to learn power query to be a pro, thanks for the solution I was doing all this type of things like that manually one by one column Keep it up best of luck for the future
Wow Chandeep Amazing Video, Really leant so many new things from this tutorial, which is going to help me in my daily repeated tasks. Salute you for your dedications. One more thing I want to learn the basic fundamentals of Power Query, So how can I learn it, please suggest or develop the Videos regarding this.
Thank you so much for sharing your precious knowledge! Would be acceptable in the Step "Allocation" the following code? = Table.AddColumn( Split, "Allocation", each [Value] / List.Product( List.Transform( Record.FieldValues( Record.SelectFields(_, AllocationCols) ), each List.Count(_) ) ) )
Super nice Chandeep. I have a special case. I am retrieving data from a Sharepoint list, and some of the columns are HTML (due to richtext format). In order to get the data of these columns I need to expand them using FieldValuesAsText. Problem: query is very slow, I suppose that it makes an individual HTTP call for each and every expanded cell to get the data. Are there any workarounds or settings I should check? Many thanks for providing your amazing content to the community.
In my opinion best method to extract data from SP list is MS access. In case you are passing your data to BI then you have no option but to use Power Query
Q: Hey I have a data and wanted to allocate it equally to a group of people with their skills in a table and based on their skills set also skill set is available in data, how we can do equal allocation to all people with the help of mcode or any other solution.
Yeah, for something that is so integral to the way M code works, there is remarkably little instruction to explain when to use one versus the other. I feel like it is some kind of conceptual thing that I'm just not getting.
Hi chandeep, I have this challenge in power query split columns let's assume we have 2 columns column1 data 1,2 and column2 data 3,4 ... How can we split those into a table like row1: column1(1),column2(3) row2:column1 (2),column2(4)..
Great video sir, but when i try to write the allocation columns, i get an error data table not found, idk, why i am getting the error, i have rechecked all the names and everything, but still the error still persists, what could be the reason, if you could put some light on it
Please suggest me i am using SharePoint online list as data source in Power BI here i am facing problem if SharePoint online list exceeds more than 5000items it is not refreshing can you please suggest how to resolve
can u copy the code and paste it it is not working can u correct it = Table.TransformColumns(Source, {{"Location", each Text.Split(_, ",")}, {"Business", each Text.Split(_, ",")}, {"Department", each Text.Split(_, ",")}})
OK, I want to make this official: I don’t understand your Split step. Not that I matter in the greater scheme of things (on any scale for that matter), but it does mean that I think that you did not explain that step well enough, IMO. Those curly brackets really threw me off balance. Could you be bothered to explain that in a bit more detail? Thanks. :-)
To understand curly brackets you'll have to understand the concept of Lists. I think my previous video would be helpful, it talks about the table.transformcolumns function in general. Give it a go, let me know if it helped! ua-cam.com/video/E6edg0ldufA/v-deo.html
@@GoodlyChandeep Oh, the concept of lists is/was not the issue, but the fact that you used {} after an each keyword was. This was the first time I saw that. Then again you need to do that because you need a list-in-list for the TransformColumns function to work. I would have expected a List.Accumulate here, but you don’t need that because you can transform multiple columns at once. Conversely, for splitting the columns you do need List.Accumulate because you can only split one column at a time. Pretty advanced stuff, if you ask me. Thanks for showing us. :-)
I'm very glad that you liked my solution and put it very nicely in a well explained tutorial. Thanks
Great job man! Keep doing the good work 💚
👏 to you and your contributors. Waiting for the list.accumulate video now 😍
Absolutely amazing Chandeep, I must admit I used the split column by delimiter into rows way and used a grouping to get the row counts to divide by...so column names are hard coded and not dynamic but you only need to add a step for a new column and the rest works so almost there!! You are a total guru and love your work!
That was mind-blowing. That was as much art as it was technical skill.
Thanks Larmondo for your nice words 😊
Great solution. Very well explained! Thanks for sharing!
Thanks Cristian!!
Brilliant, Chandeep! Thanks for sharing.
Glad you like it 😊
Brilliant, thank you Sir Goodly!
Glad you like it 😊
Awsome, Brilliant and clearly and wornderfully presented as usual
Thanks a lot or the sharing
Thank you for your nice words 😊
Amazing video. Expecting more videos like this and a collection of Advanced videos on Power query.. Thanks a million..
Glad you like it 💚
Hi Chandeep
Would you also please group them as basic, intermediate and advanced powe query groups and put them as play lists. This will narrow down the search by levels and topic. Love your instant responses. We need more Mcode videos and examples.
Cracking technique!
Thanks! 😊
09:05 🇧🇷 Wow!!! The secret of a list with the name of the columns one can automatize some steps.
But lengthy but brilliantly explained ...You are too good in this C.... Killing it ...Thanks for sharing 🙏
Thanks for your nice words 😊
Thank you very much sir, love the video and knowledge shared
Mond blowing buddy..you are just amazing.
Thanks ✌️
mma173 👏 Awesome solution and many thanks Chandeep for demo! I've been looking / waiting for instruction such as this. With this advanced M guidance, I'm confident I'll be able to make transformations I've been dreaming of and really utilize the MS M language doc., both areas where I've been "stuck" and have hindered my growth in PQ skill. Love the List work and dynamic solutions!!
mma173 amazing, Thanks Chandeep for this video
Glad you like it 😊
Amazing solution!
Thank you 💚
Thank you very much for this high class content.
Glad you like it!
I always come back to this amazing Video. It is really a masterpiece, which shows the great opportunities of Power Query. This Video is a treasure of Knowledge. I am so thankful for this outstanding video.
That's Awesome 👍 I think this is the best channel to learn power query to be a pro, thanks for the solution
I was doing all this type of things like that manually one by one column
Keep it up best of luck for the future
Glad you think so!
You are awesome..Lots of love from India 🎉🎉
Thank you so much 😀
Great demo 😃
Thank you!!
Wow Chandeep Amazing Video, Really leant so many new things from this tutorial, which is going to help me in my daily repeated tasks.
Salute you for your dedications. One more thing I want to learn the basic fundamentals of Power Query, So how can I learn it, please suggest or develop the Videos regarding this.
Brillant.
Thanks! 😊
genius!
Thanks 😇
Awesome bro
Thanks!
Thanks for having my name at last. Looking forward to more challenging challenges.
Glad it inspired you!
Brilliant video Chandeep and super solution MMA173
Glad you like it 😊
Thank you so much for sharing your precious knowledge!
Would be acceptable in the Step "Allocation" the following code?
= Table.AddColumn(
Split,
"Allocation",
each
[Value] /
List.Product(
List.Transform(
Record.FieldValues(
Record.SelectFields(_, AllocationCols)
),
each List.Count(_)
)
)
)
Super nice Chandeep. I have a special case. I am retrieving data from a Sharepoint list, and some of the columns are HTML (due to richtext format). In order to get the data of these columns I need to expand them using FieldValuesAsText. Problem: query is very slow, I suppose that it makes an individual HTTP call for each and every expanded cell to get the data. Are there any workarounds or settings I should check? Many thanks for providing your amazing content to the community.
I'm afraid, I wouldn't have a clue to solve it.
In my opinion best method to extract data from SP list is MS access. In case you are passing your data to BI then you have no option but to use Power Query
Mille mercis pour ce partage !
Very nice advance solution. If you have two queries then how can you loop through another query in PQ? I'm learning Power Query.
Awesome, but could you share with us step of (AllocationCol) step by step as I can't catch.
Q: Hey I have a data and wanted to allocate it equally to a group of people with their skills in a table and based on their skills set also skill set is available in data, how we can do equal allocation to all people with the help of mcode or any other solution.
This is an excellent tutorial.. But I still struggle with knowing the difference between an underscore and the word each.
Yeah, for something that is so integral to the way M code works, there is remarkably little instruction to explain when to use one versus the other. I feel like it is some kind of conceptual thing that I'm just not getting.
Each is like a row in excel table while _ is a cell value
Hi chandeep,
I have this challenge in power query split columns let's assume we have 2 columns column1 data 1,2 and column2 data 3,4 ...
How can we split those into a table like row1: column1(1),column2(3)
row2:column1 (2),column2(4)..
Hi Sir, could you please guide in how to connect jira with power bi using rest api and without any usage of plug-ins... that would be of great help..
Is there a way to expand tables instead of lists in the same fashion?
🙏🙏🙏🙏🙏🙏
😊😊
I have a question. Is there an easy way to count blanks per row in power query ?
Great video sir, but when i try to write the allocation columns, i get an error data table not found, idk, why i am getting the error, i have rechecked all the names and everything, but still the error still persists, what could be the reason, if you could put some light on it
Hi for the first row if we expand the total row is 36 for all possible combination not 24.
India, UK, USA, Aus (4) * Trading, Heavy Equipment (2) * Sales, Finance, Ops (3) = 24
why i am not able to count the lists, after successfully converting two columns into another column as list?
I want to do reverse of this problen(Expand multiple rows to column)
This is amazing but tried to add new column where some rows had null but get error on Split step "We cannot convert the value null to type Text."
Instead of using Text.Split, use a Splitter function e.g. Splitter.SplitTextByDelimiter(", ") the same way I did in my solution.
Please suggest me i am using SharePoint online list as data source in Power BI here i am facing problem if SharePoint online list exceeds more than 5000items it is not refreshing can you please suggest how to resolve
can u copy the code and paste it
it is not working can u correct it
= Table.TransformColumns(Source,
{{"Location", each Text.Split(_, ",")},
{"Business", each Text.Split(_, ",")},
{"Department", each Text.Split(_, ",")}})
OK, I want to make this official: I don’t understand your Split step.
Not that I matter in the greater scheme of things (on any scale for that matter), but it does mean that I think that you did not explain that step well enough, IMO. Those curly brackets really threw me off balance. Could you be bothered to explain that in a bit more detail? Thanks. :-)
To understand curly brackets you'll have to understand the concept of Lists.
I think my previous video would be helpful, it talks about the table.transformcolumns function in general. Give it a go, let me know if it helped!
ua-cam.com/video/E6edg0ldufA/v-deo.html
@@GoodlyChandeep Oh, the concept of lists is/was not the issue, but the fact that you used {} after an each keyword was.
This was the first time I saw that. Then again you need to do that because you need a list-in-list for the TransformColumns function to work.
I would have expected a List.Accumulate here, but you don’t need that because you can transform multiple columns at once.
Conversely, for splitting the columns you do need List.Accumulate because you can only split one column at a time.
Pretty advanced stuff, if you ask me. Thanks for showing us. :-)
Amazing solution!
Thanks!
Thanks for having my name at last. Looking forward to more challenging challenges.
Glad it inspired you!
@@GoodlyChandeep Can we get more. It was so fun.