Expand Multiple Columns to Rows in Power Query - Solution
Ғылым және технология
Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
All Answers - www.goodly.co.in/expand-colum...
===== 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/
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== CHAPTERS =====
0:00 Intro
0:33 Revisiting the Question
1:42 Generic Solution
5:18 Advanced Solution - Splitting by Delimiter
9:39 Allocating in Rows
15:34 Dynamic Expansion
19:13 Results
20:23 Shoutout!
===== 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 KZread Channel Below
kzread.info/dron/GQE.html...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses
Пікірлер: 86
สุดยอดมากครับ Goodly Indeed !!!
I'm very glad that you liked my solution and put it very nicely in a well explained tutorial. Thanks
@GoodlyChandeep
2 жыл бұрын
Great job man! Keep doing the good work 💚
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!
👏 to you and your contributors. Waiting for the list.accumulate video now 😍
09:05 🇧🇷 Wow!!! The secret of a list with the name of the columns one can automatize some steps.
That was mind-blowing. That was as much art as it was technical skill.
@GoodlyChandeep
2 жыл бұрын
Thanks Larmondo for your nice words 😊
Thank you very much sir, love the video and knowledge shared
mma173 amazing, Thanks Chandeep for this video
@GoodlyChandeep
2 жыл бұрын
Glad you like it 😊
Amazing solution!
@GoodlyChandeep
Жыл бұрын
Thanks!
Cracking technique!
@GoodlyChandeep
2 жыл бұрын
Thanks! 😊
Brilliant, Chandeep! Thanks for sharing.
@GoodlyChandeep
2 жыл бұрын
Glad you like it 😊
Amazing video. Expecting more videos like this and a collection of Advanced videos on Power query.. Thanks a million..
@GoodlyChandeep
2 жыл бұрын
Glad you like it 💚
@tlsrinivasan
2 жыл бұрын
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.
Great solution. Very well explained! Thanks for sharing!
@GoodlyChandeep
2 жыл бұрын
Thanks Cristian!!
Brilliant, thank you Sir Goodly!
@GoodlyChandeep
2 жыл бұрын
Glad you like it 😊
Awesome, but could you share with us step of (AllocationCol) step by step as I can't catch.
Awsome, Brilliant and clearly and wornderfully presented as usual Thanks a lot or the sharing
@GoodlyChandeep
2 жыл бұрын
Thank you for your nice words 😊
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
@GoodlyChandeep
Жыл бұрын
Glad you think so!
But lengthy but brilliantly explained ...You are too good in this C.... Killing it ...Thanks for sharing 🙏
@GoodlyChandeep
2 жыл бұрын
Thanks for your nice words 😊
Brilliant video Chandeep and super solution MMA173
@GoodlyChandeep
2 жыл бұрын
Glad you like it 😊
Brillant.
@GoodlyChandeep
2 жыл бұрын
Thanks! 😊
Mond blowing buddy..you are just amazing.
@GoodlyChandeep
Жыл бұрын
Thanks ✌️
You are awesome..Lots of love from India 🎉🎉
@GoodlyChandeep
Жыл бұрын
Thank you so much 😀
Thanks for having my name at last. Looking forward to more challenging challenges.
@GoodlyChandeep
2 жыл бұрын
Glad it inspired you!
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!!
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(_) ) ) )
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.
genius!
@GoodlyChandeep
2 жыл бұрын
Thanks 😇
Awesome bro
@GoodlyChandeep
2 жыл бұрын
Thanks!
Great demo 😃
@GoodlyChandeep
2 жыл бұрын
Thank you!!
Thank you very much for this high class content.
@GoodlyChandeep
Жыл бұрын
Glad you like it!
@karimallahwala7022
Жыл бұрын
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.
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.
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)..
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.
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..
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
Is there a way to expand tables instead of lists in the same fashion?
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
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.
@GoodlyChandeep
2 жыл бұрын
I'm afraid, I wouldn't have a clue to solve it.
@3Starsgamer34
Жыл бұрын
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
This is an excellent tutorial.. But I still struggle with knowing the difference between an underscore and the word each.
@larmondoflairallen4705
2 жыл бұрын
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.
@3Starsgamer34
Жыл бұрын
Each is like a row in excel table while _ is a cell value
I have a question. Is there an easy way to count blanks per row in power query ?
🙏🙏🙏🙏🙏🙏
@GoodlyChandeep
2 жыл бұрын
😊😊
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)
Hi for the first row if we expand the total row is 36 for all possible combination not 24.
@ShavitTube
2 жыл бұрын
India, UK, USA, Aus (4) * Trading, Heavy Equipment (2) * Sales, Finance, Ops (3) = 24
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."
@user-mma173
2 жыл бұрын
Instead of using Text.Split, use a Splitter function e.g. Splitter.SplitTextByDelimiter(", ") the same way I did in my solution.
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. :-)
@GoodlyChandeep
2 жыл бұрын
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! kzread.info/dash/bejne/d2qZxslpnMfblqQ.html
@GeertDelmulle
2 жыл бұрын
@@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!
@GoodlyChandeep
2 жыл бұрын
Thank you 💚
Thanks for having my name at last. Looking forward to more challenging challenges.
@GoodlyChandeep
2 жыл бұрын
Glad it inspired you!
@MANATTOWN
2 жыл бұрын
@@GoodlyChandeep Can we get more. It was so fun.