Creating all possible joined text combinations of three tables (Power Query)

[didijaba]

Hi,
I would like to create table that has all possible combinations of values from three different tables (without changing places of table columns in joined string). So I have one table with list of all countries, second table with months (1-12) and last table with names of people. So my final result would be table with one column and all possible strings in form COUNTRY&MONTH&NAME.
Thanks in advance for any advice.
With regards,

 

[Comfy]

You can use a Many to Many join:

let
//Country Source - Change type - add custom column with 1 for all rows for many to many join
tCountry = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvVWitUBUsFKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Country = _t]),
CountryType = Table.TransformColumnTypes(tCountry,{{"Country", type text}}),
CountryCustom = Table.AddColumn(CountryType, "Custom", each 1),


//Month Source - Change type - add custom column with 1 for all rows for many to many join
tMonth = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi3ApCWYNDSAUBDdhkDtsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t]),
MonthType = Table.TransformColumnTypes(tMonth,{{"Month", Int64.Type}}),
MonthCustom = Table.AddColumn(MonthType, "Custom", each 1),

//Name Source - Change type - add custom column with 1 for all rows for many to many join
tName = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUorViVZySSxLBTOCE/NSihKVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t]),
NameType = Table.TransformColumnTypes(tName,{{"Name", type text}}),
NameCustom = Table.AddColumn(NameType, "Custom", each 1),

MergeCountryMonth = Table.NestedJoin(CountryCustom,{"Custom"},MonthCustom,{"Custom"},"NewColumn",JoinKind.LeftOuter),
ShowMonth = Table.ExpandTableColumn(MergeCountryMonth, "NewColumn", {"Month"}, {"NewColumn.Month"}),
MergeWithName = Table.NestedJoin(ShowMonth,{"Custom"},Name,{"Custom"},"NewColumn",JoinKind.LeftOuter),
ShowName = Table.ExpandTableColumn(MergeWithName, "NewColumn", {"Name"}, {"NewColumn.Name"}),
#"Removed Columns" = Table.RemoveColumns(ShowName,{"Custom"}),
#"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "Merged", each Text.Combine({Text.From([Country], "en-GB"), Text.From([NewColumn.Month], "en-GB"), Text.From([NewColumn.Name], "en-GB")}, ""), type text),
#"Removed Other Columns" = Table.SelectColumns(#"Inserted Merged Column",{"Merged"})
in
#"Removed Other Columns"

 

[didijaba]

Thanks Comfy, I will try this

 

[billszysz]

let

   fxExpand = (tbl as table, headers as list, n as number) =>
       let
         Expand = Table.ExpandListColumn(tbl, headers{n}),
         Condition = if n = List.Count(headers)-1 then Expand else @fxExpand(Expand, headers, n+1)
       in
         Condition,

    lstCountry = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvVWitUBUsFKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Country = _t])[Country],
    lstMonth = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi3ApCWYNDSAUBDdhkDtsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t])[Month],
    lstName = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUorViVZySSxLBTOCE/NSihKVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t])[Name],
    TblToExpand = Table.FromColumns({{lstCountry},{lstMonth},{lstName}}, {"Country","Month","Name"}),
    HeadersToExpand = Table.ColumnNames(TblToExpand),
    ExpandAll = fxExpand(TblToExpand, HeadersToExpand, 0),
    #"Merged Columns" = Table.CombineColumns(ExpandAll,{"Country", "Month", "Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Result")
in
    #"Merged Columns"

 

[didijaba]

Hi billszysz,
can you be so kind and explain to me part with "lstCountry = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvVWitUBUsFKsbEA....". How did you come to that? Thanks for everything so far, I learned a lot .

 

[Comfy]

HI didi,

In Power BI Desktop you can enter data manually, the application automatically generates that code.

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-enter-data-directly-into-desktop/

Click Enter Data,
Paste in your table,
View the code in the advanced editor.

 

[didijaba]

Thanks, I was afraid it was something really complicated

 

[Comfy]

Me too, I asked the same question not so long ago.

 

[didijaba]

Can you you do me one more favor? How would script look if I used known tables from excel file? I have tables named COUNTRY, MONTH,NAME and I would like for combined table to self correct (expand) when new values are added to COUNTRY, MONTH,NAME tables. Thanks in advance

 

[Comfy]

You only need to amend the data sources in billszysz answer:

This assumes you have three tables:
Table1 is called "CountryTbl" and has a column "Country"
Table2 is called "MonthTbl" and has a column "Month"
Table3 is called "NameTbl" and has a column "Name"

let


   fxExpand = (tbl as table, headers as list, n as number) =>
       let
         Expand = Table.ExpandListColumn(tbl, headers{n}),
         Condition = if n = List.Count(headers)-1 then Expand else @fxExpand(Expand, headers, n+1)
       in
         Condition,


    lstCountry = Excel.CurrentWorkbook(){[Name="CountyTbl"]}[Content][Country],
    lstMonth = Excel.CurrentWorkbook(){[Name="MonthTbl"]}[Content][Month],
    lstName = Excel.CurrentWorkbook(){[Name="NameTbl"]}[Content][Name],
    TblToExpand = Table.FromColumns({{lstCountry},{lstMonth},{lstName}}, {"Country","Month","Name"}),
    HeadersToExpand = Table.ColumnNames(TblToExpand),
    ExpandAll = fxExpand(TblToExpand, HeadersToExpand, 0),
    #"Merged Columns" = Table.CombineColumns(ExpandAll,{"Country", "Month", "Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Result")
in
    #"Merged Columns"