Multiply two or more tables

Question

Hi,&nbsp;&nbsp;I am new to power BI and i would like to combine two main tables into one. However, they are not of the same size.&nbsp;&nbsp;Table 1:ProductCodePercentageProd1A30%Prod1B30%Prod1C40%Prod2A25%Prod2F25%Prod2H50%Prod3I15%Prod3J25%Prod3D25%Prod3E35%Prod4F14%Prod4C21%Prod4G30%Prod4B35%&nbsp;Table 2:CategoryCodeQ1 2018Q2 2018Q3 2018Q4 20181A1.11.21.31.32B0.80.80.70.63C3.23.23.133D0.40.30.20.12E0.70.60.650.621F2.52.42.62.72G3.13.23.23.13H4.44.44.34.21I2.22.12.22.1&nbsp;This is how the data is being stored. I would like to have the following output:ProductQ1 2018Q2 2018Q3 2018Q4 2018Prod1&nbsp;&nbsp;&nbsp;&nbsp;Prod2&nbsp;&nbsp;&nbsp;&nbsp;Prod3&nbsp;&nbsp;&nbsp;&nbsp;Prod4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;How do i create the table?&nbsp;Thank you!&nbsp;

smpa01 · Accepted Answer

Hi.Anonymous&nbsp;You can achieve the view by this&nbsp;let
    Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Multiply-two-or-more-tables/m-p/662123#M318163")),
    Data0 = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product", type text}, {"Code", type text}, {"Percentage", Percentage.Type}})
in
    #"Changed Type"Table 0let
    Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Desktop/Multiply-two-or-more-tables/m-p/662123#M318163")),
    Data1 = Source{1}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data1, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category", Int64.Type}, {"Code", type text}, {"Q1 2018", type number}, {"Q2 2018", type number}, {"Q3 2018", type number}, {"Q4 2018", type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Code"},#"Table 0",{"Code"},"Table 0",JoinKind.LeftOuter),
    #"Expanded Table 0" = Table.ExpandTableColumn(#"Merged Queries", "Table 0", {"Product"}, {"Product"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Table 0",{"Product", "Q1 2018", "Q2 2018", "Q3 2018", "Q4 2018"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Product"}, {{"Q1 2018", each List.Sum([Q1 2018]), type number}, {"Q2 2018", each List.Sum([Q2 2018]), type number}, {"Q3 2018", each List.Sum([Q3 2018]), type number}, {"Q4 2018", each List.Sum([Q4 2018]), type number}})
in
    #"Grouped Rows"Table 1&nbsp;

Forum Discussion

Multiply two or more tables

2 Replies

Related content

Multiply two tables

Multiply values in columns (Different tables)

Multiplying between Tables

DAX multiply two columns in separate tables

multiplier

Product	Code	Percentage
Prod1	A	30%
Prod1	B	30%
Prod1	C	40%
Prod2	A	25%
Prod2	F	25%
Prod2	H	50%
Prod3	I	15%
Prod3	J	25%
Prod3	D	25%
Prod3	E	35%
Prod4	F	14%
Prod4	C	21%
Prod4	G	30%
Prod4	B	35%

Category	Code	Q1 2018	Q2 2018	Q3 2018	Q4 2018
1	A	1.1	1.2	1.3	1.3
2	B	0.8	0.8	0.7	0.6
3	C	3.2	3.2	3.1	3
3	D	0.4	0.3	0.2	0.1
2	E	0.7	0.6	0.65	0.62
1	F	2.5	2.4	2.6	2.7
2	G	3.1	3.2	3.2	3.1
3	H	4.4	4.4	4.3	4.2
1	I	2.2	2.1	2.2	2.1