2 Tricks to Create Unique Combinations From Multiple Columns - Power Query #7
Vložit
- čas přidán 14. 07. 2024
- This video shows how you can create unique combinations from a set of columns using Power Query.
WRITTEN BLOGPOST:
gorilla.bi/power-query/create...
Master Functions and Syntax in M
powerquery.how
ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.
SUPPORT MY CHANNEL
Any videos are made free of charge. You can support my channel by giving a donation through: paypal.me/rickmaurinus.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
czcams.com/users/bigorilla?sub_con...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#PowerBI #PowerQuery #BIGorilla
To learn more about functions and syntax make sure to check out: powerquery.how/
Enjoy!
I have watched quite a few methods to come up with a solution for generating a unique combination for my upcoming NFT collection, this one has worked like magic, I thank you sincerely for teaching this method like a champ! Kudos!
Second method is pure magic! Thank you!
Thanks for the support!! 🙌
Awesome explanation, thank you!
You just saved my life! thanksssssssssss. This is perfect and you explained it so well. Great Job.
I have been searching and trying a lot, even with Chat GPT, but this one worked perfect! Thank you so much :)
Thanks Rick. Perfect 👍👍👍
Rick: There is a much more easier way to create a cartesian product in PQ, If you upload all your tables as a connection. Once all the 3 tables are created as a connection, on the first table ( Countries) create a custom column as follows = # " businessunits " . Then expand the columns and you will see the unique combination for those 2 tables. Repeat the same step for the 3rd table. I recreated your example and I am getting the same result as you ( 4X4X4 = 64 rows) Great video and a great refresher.
Thanks jazzista. That's exactly what the second method in this video describes. Well found 👌
Thank you - the second way is perfect! :-)
Great!
Wow, cool trick man. Thanks!
Thanks Luciano, glad you liked it!
Thanks! you saved my life :)
Glad it helped you out! :)
Thank you it is very helpful.
I'm happy it helped!
Cool!
Great explanation
Thanks and great having you on the channel @gang of Guns
Thanks for this. I'm brute forcing the passcode security on Family controls for the XBOX 360 and needed a way to generate 10,000 combinations.
Hi Hayden. Great use case! Hope it works out!
Yes combination 193 was the winner lol
Great video. Would be cool if you can develop that a little further and show how to use this table to filter throughout multiple reports. This would also differntiate your channel from others.
Hi Raymond! Thanks for your response. I welcome input to improve my channel and hope the videos help you in the right direction. Could you elaborate on what you mean with ''how to use this table to filter throughout multiple reports"?
@@BIGorilla Sure I can, in the beginning of the video you were talking about the use case on how to apply this matching table in order to get the right totals from different reports. I think many people dont really know how to do that so it would be cool to see that on a real case. I sort of know it, but I d like to know more about it.
@@raymondpocher2034 it's been two years and my channel was off for a while. Sorry for the lost response. I just wanted to reach out still that I did not fully grasp your comment. I hope the newer videos do match your expectations better. The production has improved and the topics are wider now. Any suggestions are appreciated as always
great video, wat if i dont want all possible combinations, for example not with full table or all rows just specific rows, first name with row 2 3 4, second row with 4 5 6 ? again great video !!
Thank you! Finally someone has showed me the way how make my fucking task by job)
Happy to help!
Thank you! how did you set your default save for power query as connection? Mine is set to table and I think saving as connection would be better to have as default
Hi there,
You can adjust it in the query options settings of Power query :
images.app.goo.gl/5rJmaEnEsVQynmnk6
@@BIGorilla thanks!
I was doing word column combinations in my excel.
And rows are continuing loding till now. So can I save it.. for stop loading rows and catch up already loaded rows. ?????
If I have a table of 16 rows (First table), and each row can get one of 3 values (Second table), then the total combinations should be 16^3=4096, how can I achieve it?
Does anybody knows how to see the original data tables before creating the query connections? I was creating each connection for each of my tables and on the query editor I accidentally clicked "Close and load" instead of "Close and load to...", so my original tables disappeared and I can't find them.
can you show me how to do 10 names with 1 and 2 number combinations there is 1024 i want to create a sheet like that
Hey bebeknaz. I'm not sure I understand. Would you want to see.
Bebeknaz1
Bebeknaz2
......
Bebeknaz99
Excelgorilla1
Excelgorilla2
.....
Excegorilla99.
Can you give an example?
For EX#1, Why do you get a permutation of combinations as per the definition of "Full Outer" you should expect ALL rows from both tables? For EX#2, you imported the desired Tables without having to relate them. I'm doubting Relational databases now 😄....I assume the relations are established in the background.
I have a question is it possible to generate combination or arrangement can be repeated with 24 combinations with the letters A and B and I want to know how many times we can generate combinations of 24 with these letters
Hi! If you have a row with the values A and B, then you can repeat your values by using the function Table.Repeat in Power Query.
Hope that helps !
Rick
@@BIGorilla sorry but would you have some example to guide me
Developer : Tries to learn excel and power query.
Developer after 5 mins: Goes back to sql
Haha hold on Thyag. Power query is great! Makes some transformations much easier than sql. Try capitalising each word or split a cell by each delimiter... 😁
You will master it if you stick to it just a little longer!
@@BIGorilla I think I was impatient to get something fast. I will start from the fundamentals. Thanks for the inspiration🙏
Dear sir, i nead a combination chart
I'm not sure how it's related to this video. Please elaborate :)
hi sir pls upload video a video ,,, i have a 16 names, i want only 08 names for different combinations, like ex:1,2,3,4,5,6,7,8,9, i have 9 no.s i want only 3no.s 1,2,3, 2,3,4 ,1,2,4 , 3,5,2, ,5,6,9 non repet combination pls tell me how to create in excel
He Yarra. Thanks for watching my videos. I'm not sure I follow. Can you elaborate? Maybe I can help