CONSOLIDATION

mohsin91santono-_-1220 

Assign a name:

Data =Concatenate!$A$2:$P$5

Get data & transform from table/named range.

Now you are in the query editor.

Delete the "Changed type" step.

Transform -> First row as headers.

Delete the "Changed type" step.

Select the columns and change the type via Transform -> Date type:

Style as text, Color as text, Start and End as date.

Home -> Close and load to ... connection only.

Double click on the query name in the query pane.

Home -> Manage -> Reference.

Rename the query to "qry_Result".

Remove the columns "Start", "End", "Per day", "leadtime", "PO qty".

Select "Style", "Color" and "PO number".

Transform -> Unpivot other columns.

Change data type for "Date" to date/time.

Change data type for "Date" to date as new step.

Change data type for "Value" to decimal.

Select "Style", "Color" and "Date".

Home -> Group by.

First grouping:

Value - Sum - Value

Second grouping:

AllData - All rows

Add column -> Custom column "PO number" with formula:

=[AllData][PO number]

Click on the double arrow of column "PO number".

Select "extract".

Choose the delimiter.

Remove columns "AllData".

Select column "Value" and "PO numbers".

Transform -> Merge columns.

Select delimiter and add the new column name "Value,PO numbers".

Home -> Close and load to ... (not Close and load).

Select "PivotTable report" and check "Add to Data model".

Add a new measure as shown in the link I provided in the workbook.

Build the pivot table.