How to multiply Table with table in Power BI

[Anonymous]

Hello,

Can you explain how we can multiply each row of Table 2 with Table 1? So we can achieve table 3? 

Thank you

Regards

Ahmet

[v-alq-msft]

Hi, @Anonymous 

You may achieve your requirement in 'Query Editor' or Power BI Desktop. I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

Table2:

In 'Query Editor', you may duplicate query 'Table1', remove column 'Surname', add a custom column and then expand the column with lists.

Here are codes in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsyixW0lFyrFKK1YlW8srPyAPy/CG84JLUtEQQ3xPIjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Surname = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Surname", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Surname"}),
    Custom1 = Table.AddColumn(#"Removed Columns","Title",each Table2[Title]),
    #"Expanded Title" = Table.ExpandListColumn(Custom1, "Title")
in
    #"Expanded Title"

Result:

In Power BI Desktop, you may create a calculated table as below.

Table = 
CROSSJOIN(
   DISTINCT('Table2'[Title]),
   DISTINCT(Table1[Name])
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Ahmet_Oz , Crossjoin

Cross-joint(Table1, Table2)