SOLVED

CONSOLIDATION

Brass Contributor

Hello there,

 

My attached picture is self explanatory.

Please help me to provide a good formula.

 

Thanks in advance

11 Replies

@Celia_Alves 
Hello Celia,
Could you please help?

I am back again for your advice. :)

Hello!
Could you please provide an excel file with the sample data?

@mohsin91santono-_-1220 

Solved with Power Query, Data Model and Pivot.

 

@Detlef Lewin 
Hi Detlef
Thanks. But how did you do it?
I have no idea about power query.
Also, i changed something inside pivot, not it is not working.
Don't know why.

Can you help me without pivot?
I need as a simple chart.

best response confirmed by mohsin91santono-_-1220 (Brass Contributor)
Solution

@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.

 

@Detlef Lewin 

Thanks for your patience to explain :D

@Detlef Lewin 
Hi, Again I need your help.

Could not connect something.

Please check below image and help me again :)

 

I came till step "First Grouping:" and I stopped.


2019-06-02_12h35_36.png

@mohsin91santono-_-1220 

Value - Sum - Value refer to the fields New column name - operation - Column.

And then click on "Add aggregation" for the second grouping.

 

@Detlef Lewin 
Hi, but date column is vanished after doing this :(
Not sure why. :(
But, thanks for opening power query for me. Totally new idea and I am exploring.

But, shall be grateful if you could help me to find out the root cause of missing "date" column.

@mohsin91santono-_-1220 

Compare the code in my workbook with the code in your workbook. Maybe you can spot some differences.

There are some steps in which I incorporated column renaming by editing the code.

1 best response

Accepted Solutions
best response confirmed by mohsin91santono-_-1220 (Brass Contributor)
Solution

@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.

 

View solution in original post