How to dynamically add rows to column in power bi/power query

Question

I'm facing a challenge in power bi where I need to join Table 1 & Table 2 but the catch is Table 2 needs to be pivoted before joining.

Input: Table 1

Table 2

Expected Output:

How to build the output table when the Table 2 rows will be increasing daily

Gangula · Accepted Answer · 2022-01-16 11:11:09Z

1

Your desired result needs a combination of Unpivot, Merge and Pivot steps.

You can follow the following steps:

Convert the Date column to Text type.
Unpivot columns Sales in KG & Sales in Amount from Table 2 - it will create 2 columns called Attribute & Value
Merge columns Date & Attribute to create a new column (lets call it columnHeaders) - this will be in the format - 1/3/22 Sales in KG, 1/3/22 Sales in Amount ...
Merge Table 1 into Table 2 and expand the Product Name column
- Now you will have 4 columns - Product Code, columnHeaders, Value, & Product Name
Pivot columnHeader using the Value column for values

You should have your desired result.

answered Jan 16, 2022 at 11:11

Gangula

7,8205 gold badges54 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

msta42a · Accepted Answer · 2022-01-16 09:35:44Z

0

I dont know why you try to unpivot your table. Just use a Matrix visualization:

Model relationship: Imput data + output:

sum of kg = CALCULATE(sum(Table2[Sales in kg]))

answered Jan 16, 2022 at 9:35

msta42a

3,7491 gold badge7 silver badges17 bronze badges

I’m trying to un pivot because I need add furthermore calculation - weekly return, weekly growth & group products and then show it in visualisation