Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to Solution.
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.