Hi all readers/problem solvers
Hope someone can educate on how, using Power Query, one can step from this not so happy/stuck place here 
To here:
I would like to have Power Query generate the list of all combinations, but within the Regions grouping.
Assistance would be much appreciated.
Sample data attached.
Generate combination.pbix (21.6 KB) Generate sample.xlsx (15.0 KB)
Hi @Mo.jo.jo,
Not near my computer at the moment but can you see if this is helpful to you.
See if this does the trick:
Create Region Groups table:
let
Source = Excel.Workbook(File.Contents(#"File Location"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"User ID", type number}, {"Group Index", type number}, {"Group Name", type text}, {"Region", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"User ID", "Region", "Group Index", "Group Name"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Group Name", "User ID"})
in
#"Removed Columns"
Create Result table:
let
Source = Table.NestedJoin(#"Table 1", {"Region"}, #"Region Groups", {"Region"}, "Region Groups", JoinKind.LeftOuter),
#"Expanded Region Groups" = Table.ExpandTableColumn(Source, "Region Groups", {"Region", "Group Index"}, {"Region.1", "Group Index.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Region Groups",{"Group Index", "Group Name", "Region.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Group Index.1", "Group Index"}})
in
#"Renamed Columns"
I hope this is helpful. Full solution file attached below.
– Brian
eDNA Forum - Generate combination Solution.pbix (34.5 KB)
@Melissa
Thanks for the link. Really interesting techniques showcased in that article. Both the lengthier process and the quick version.
I think the steps draw parallels with the technique utilised by Brian in his solution.
@BrianJ
Much obliged for putting the steps together.
I think I got stuck on thinking that this had to be done within one table using some list within a table function that I have no idea about.
I think I can combine your work with the link Melissa sent to get the desired end result in the real world scenario 
I’m sure there IS an elegant way of doing this with one table. My PQ technique is still best described as “bull meets china shop” - it gets to the desired end point, but the process isn’t pretty…
I get the feeling that the Bull in China Shop (BiCS) club has quite a broad membership. Some with more or less china in the shop when the bull entes. Thanks for pointing out that out.
Yet still you both are able to work your way round to the desired effect. Or at least help people get to the end result.
I increasing feel the need for some meatier Power Query course content to get more comfortable not being spoon-fed by the PQ user interface. Currently, if the UI does not make it obvious how something can be done, or it is not obvious to me how I can do this by combining multiple queries (the BiCS) or the forum does not bail me out then I am stumped.
Not a competence-inducing feeling. But very grateful for the expertise and shared knowledge in these forums
I increasingly feel the need for some meatier Power Query course content
This has been a big topic of conversation lately with Sam and the expert team, since I think for almost all of us on the site our DAX skills far outstrip our PQ/M skills. To help address this imbalance, @sam.mckay, as he discussed on a recent AMA chat, has enlisted @melissa to create a full PQ/M module within the Enterprise DNA training portal.
In the interim, here are three resources that have really helped me advance my PQ/M skills:
Ken Puls’ Power Query Academy training - @Nick_M first recommended this to me, and he was right – it’s excellent. I would characterize it as “Enterprise DNA for Power Query”. Lots of training videos, and a good user forum all dedicated entirely to Power Query.
Collect, Combine, and Transform Data Using Power Query in Excel and Power BI by Gil Raviv - until we can get @Melissa to write “The Definitive Guide to Power Query and M”, this is the next best thing.
@Melissa’s Enterprise DNA TV YouTube Videos - this is the best advanced Power Query/M content on the Internet IMO. I think she’s currently up to video #14, and these are just a master class in advanced PQ technique. These are not a casual watch – I often have to watch them multiple times to fully absorb the techniques, but if you put in the effort it will have a huge impact on your PQ skills.
@AntrikshSharma - that is definitely my favorite post on the forum this year. 
Awesome @BrianJ
I came across the Ken Pul’s site at the weekend and was going to look more into that. And yes, Melissa’s stuff is top top stuff. Not casual at all. I feel like I need some baby steps to eventually wrap my head around some of the way she resolves the issues, but it’s really good to be shown how powerful Power Query is.
I am glad to hear that this topic is being discussed by those who can do something about this (i.e. produce more content on this) within EDNA.
It is incredible that it has taken me this long to realise how reliant I am on the user interface 
, which I guess comes with trying to take on more demanding data sets. I read a lot that your DAX code can be a lot simpler if your data model is set up well, but how you transform your data and how important that can be is often not highlighted. So most of us take the detour to face/start on the DAX mountain trek trail. But muscles you develop tacking Power Query might help make parts of tacking the DAX thing simpler.
Thanks BrianJ. I will look into all those links and try and somehow fit some content in.
And I guess we tag @Melissa with every Power Query question until she’s fed up and writes that tome 
LOL
I guess the only downside to that will be that there’s not enough time in the day left to create any content to structurally change that situation 
That is 100% accurate in every regard, to the point that it’s become a running joke between me and @Melissa. I will provide a 40-line DAX solution to a forum question, and Melissa will accomplish the same thing in 10 lines of PQ/M. We were originally going to do a series of video showdowns of DAX versus PQ on the same problem, until we both came to the conclusion that it would basically just be re-running this gif over and over:
Obviously, there are certain dynamic situations where only DAX is appropriate, but I’ve been surprised by how much less DAX I actually write these days. If you look at my PBIX file for Challenge #7, you’ll see that a large portion of my DAX is just harvesting slicers and manipulating the URLs for icons and the timeline graphics.
Also, there is absolutely no shame in relying very heavily on the PQ user interface. I think Melissa would tell you that she tries to maximize the use of the UI, and only write custom M when the functionality is just not possible through the UI alone.
Hi @Mo.jo.jo,
I know your question has already been solved but as an alternative you can test this query. Just copy and paste the M code below to a new blank query.
What it does is it creates a list of the Group Index for each record
and expands the content of that list to new rows.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXL3UwBRQanpmfl5YKaRsVKsTrSSEUTSCFXS0AQsaQyRNEbTCZYzNIAaa4CQBZliZACRhtlqiCptArUWZi+SxSBrzCwg0lCbjYxRpY1MlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Group Index" = _t, #"Group Name" = _t, Region = _t, #"User ID" = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"User ID", type number}, {"Group Index", type number}, {"Group Name", type text}, {"Region", type text}}),
GroupRows = Table.Group(ChangeType, {"Region"}, {{"Table", each _, type table [Group Index=nullable number, Group Name=nullable text, Region=nullable text, User ID=nullable number]}}),
AddedCustom = Table.AddColumn(GroupRows, "Custom", each
let
myTable = [Table],
AddCustom = Table.AddColumn(myTable, "Group Index New", each myTable[Group Index]),
ExpandNew = Table.ExpandListColumn(AddCustom, "Group Index New"),
NewType = Table.TransformColumnTypes(ExpandNew,{{"Group Index New", Int64.Type}}),
CleanUpColumns = Table.RemoveColumns(NewType,{"Group Index", "Group Name"})
in
CleanUpColumns, type table )[[Custom]],
ExpandCustom = Table.ExpandTableColumn(AddedCustom, "Custom", {"Region", "User ID", "Group Index New"}, {"Region", "User ID", "Group Index New"}),
DetectType = Table.TransformColumnTypes(ExpandCustom,{{"Region", type text}, {"User ID", Int64.Type}, {"Group Index New", Int64.Type}})
in
DetectType
.
With this result
@BrianJ
Hearing that Power Query often provides a more efficient path to a Power BI solution than DAX is so surprising. How is that possible? It seems quite primative and barely has functioning intellisense 
I think the proposed sessions with you and Melissa should be a light-hearted Christmas season series. 12 days of Christmas but Power Query (12 slays of …DAX).lol.
@Melissa Shame you don’t have time in the near future to put something comprehensive together. If you ever do put something together you can sign me up for proof-reading or content texting 
For now, guided inspiration from you and the other experts will take us much further than we could go alone.
And many thanks for taking time to put together a more elegant/uncracked china solution. This is also getting tagged as the solution if that is possible.
Seriously cool how that nested let combination works. Seems to work like an inner loop thing and quite a useful function/command thing to witness.
Thank you all 
You could also turn those nested transformation steps into a separate function (query) and invoke that - it will clean up the code. But as we have only one table here, to perform these transformations on, it kinda makes more sense to me to have all the steps in one single query…
BTW if you check the eDNA YouTube channel you’ll find this video which shows a hack of how to easily create that nested logic by just using the User Interface.
