SOLVED
Home

CONSOLIDATION

%3CLINGO-SUB%20id%3D%22lingo-sub-657503%22%20slang%3D%22en-US%22%3ECONSOLIDATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657503%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20attached%20picture%20is%20self%20explanatory.%3C%2FP%3E%3CP%3EPlease%20help%20me%20to%20provide%20a%20good%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-657503%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-657562%22%20slang%3D%22en-US%22%3ERe%3A%20CONSOLIDATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657562%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302344%22%20target%3D%22_blank%22%3E%40Celia_Alves%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EHello%20Celia%2C%3CBR%20%2F%3ECould%20you%20please%20help%3F%3C%2FP%3E%3CP%3EI%20am%20back%20again%20for%20your%20advice.%20%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-657779%22%20slang%3D%22en-US%22%3ERe%3A%20CONSOLIDATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657779%22%20slang%3D%22en-US%22%3EHello!%3CBR%20%2F%3ECould%20you%20please%20provide%20an%20excel%20file%20with%20the%20sample%20data%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-657785%22%20slang%3D%22en-US%22%3ERe%3A%20CONSOLIDATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657785%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302344%22%20target%3D%22_blank%22%3E%40Celia_Alves%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-657891%22%20slang%3D%22en-US%22%3ERe%3A%20CONSOLIDATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657891%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302407%22%20target%3D%22_blank%22%3E%40mohsin91santono-_-1220%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESolved%20with%20Power%20Query%2C%20Data%20Model%20and%20Pivot.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-657936%22%20slang%3D%22en-US%22%3ERe%3A%20CONSOLIDATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657936%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EHi%20Detlef%3CBR%20%2F%3EThanks.%20But%20how%20did%20you%20do%20it%3F%3CBR%20%2F%3EI%20have%20no%20idea%20about%20power%20query.%3CBR%20%2F%3EAlso%2C%20i%20changed%20something%20inside%20pivot%2C%20not%20it%20is%20not%20working.%3CBR%20%2F%3EDon't%20know%20why.%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20you%20help%20me%20without%20pivot%3F%3CBR%20%2F%3EI%20need%20as%20a%20simple%20chart.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-658042%22%20slang%3D%22en-US%22%3ERe%3A%20CONSOLIDATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-658042%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302407%22%20target%3D%22_blank%22%3E%40mohsin91santono-_-1220%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssign%20a%20name%3A%3C%2FP%3E%3CP%3EData%20%3DConcatenate!%24A%242%3A%24P%245%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGet%20data%20%26amp%3B%20transform%20from%20table%2Fnamed%20range.%3C%2FP%3E%3CP%3ENow%20you%20are%20in%20the%20query%20editor.%3C%2FP%3E%3CP%3EDelete%20the%20%22Changed%20type%22%20step.%3C%2FP%3E%3CP%3ETransform%20-%26gt%3B%20First%20row%20as%20headers.%3C%2FP%3E%3CP%3EDelete%20the%20%22Changed%20type%22%20step.%3C%2FP%3E%3CP%3ESelect%20the%20columns%20and%20change%20the%20type%20via%20Transform%20-%26gt%3B%20Date%20type%3A%3C%2FP%3E%3CP%3EStyle%20as%20text%2C%20Color%20as%20text%2C%20Start%20and%20End%20as%20date.%3C%2FP%3E%3CP%3EHome%20-%26gt%3B%20Close%20and%20load%20to%20...%20connection%20only.%3C%2FP%3E%3CP%3EDouble%20click%20on%20the%20query%20name%20in%20the%20query%20pane.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHome%20-%26gt%3B%20Manage%20-%26gt%3B%20Reference.%3C%2FP%3E%3CP%3ERename%20the%20query%20to%20%22qry_Result%22.%3C%2FP%3E%3CP%3ERemove%20the%20columns%20%22Start%22%2C%20%22End%22%2C%20%22Per%20day%22%2C%20%22leadtime%22%2C%20%22PO%20qty%22.%3C%2FP%3E%3CP%3ESelect%20%22Style%22%2C%20%22Color%22%20and%20%22PO%20number%22.%3C%2FP%3E%3CP%3ETransform%20-%26gt%3B%20Unpivot%20other%20columns.%3C%2FP%3E%3CP%3EChange%20data%20type%20for%20%22Date%22%20to%20date%2Ftime.%3C%2FP%3E%3CP%3EChange%20data%20type%20for%20%22Date%22%20to%20date%20as%20new%20step.%3C%2FP%3E%3CP%3EChange%20data%20type%20for%20%22Value%22%20to%20decimal.%3C%2FP%3E%3CP%3ESelect%20%22Style%22%2C%20%22Color%22%20and%20%22Date%22.%3C%2FP%3E%3CP%3EHome%20-%26gt%3B%20Group%20by.%3C%2FP%3E%3CP%3EFirst%20grouping%3A%3C%2FP%3E%3CP%3EValue%20-%20Sum%20-%20Value%3C%2FP%3E%3CP%3ESecond%20grouping%3A%3C%2FP%3E%3CP%3EAllData%20-%20All%20rows%3C%2FP%3E%3CP%3EAdd%20column%20-%26gt%3B%20Custom%20column%20%22PO%20number%22%20with%20formula%3A%3C%2FP%3E%3CP%3E%3D%5BAllData%5D%5BPO%20number%5D%3C%2FP%3E%3CP%3EClick%20on%20the%20double%20arrow%20of%20column%20%22PO%20number%22.%3C%2FP%3E%3CP%3ESelect%20%22extract%22.%3C%2FP%3E%3CP%3EChoose%20the%20delimiter.%3C%2FP%3E%3CP%3ERemove%20columns%20%22AllData%22.%3C%2FP%3E%3CP%3ESelect%20column%20%22Value%22%20and%20%22PO%20numbers%22.%3C%2FP%3E%3CP%3ETransform%20-%26gt%3B%20Merge%20columns.%3C%2FP%3E%3CP%3ESelect%20delimiter%20and%20add%20the%20new%20column%20name%20%22Value%2CPO%20numbers%22.%3C%2FP%3E%3CP%3EHome%20-%26gt%3B%20Close%20and%20load%20to%20...%20(not%20Close%20and%20load).%3C%2FP%3E%3CP%3ESelect%20%22PivotTable%20report%22%20and%20check%20%22Add%20to%20Data%20model%22.%3C%2FP%3E%3CP%3EAdd%20a%20new%20measure%20as%20shown%20in%20the%20link%20I%20provided%20in%20the%20workbook.%3C%2FP%3E%3CP%3EBuild%20the%20pivot%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-662362%22%20slang%3D%22en-US%22%3ERe%3A%20CONSOLIDATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-662362%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20patience%20to%20explain%20%3AD%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-663096%22%20slang%3D%22en-US%22%3ERe%3A%20CONSOLIDATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-663096%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EHi%2C%20Again%20I%20need%20your%20help.%3C%2FP%3E%3CP%3ECould%20not%20connect%20something.%3C%2FP%3E%3CP%3EPlease%20check%20below%20image%20and%20help%20me%20again%20%3A)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20came%20till%20step%20%22First%20Grouping%3A%22%20and%20I%20stopped.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116886i7FF4DAEC25CAE89F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-06-02_12h35_36.png%22%20title%3D%222019-06-02_12h35_36.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-663123%22%20slang%3D%22en-US%22%3ERe%3A%20CONSOLIDATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-663123%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302407%22%20target%3D%22_blank%22%3E%40mohsin91santono-_-1220%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EValue%20-%20Sum%20-%20Value%20refer%20to%20the%20fields%20New%20column%20name%20-%20operation%20-%20Column.%3C%2FP%3E%3CP%3EAnd%20then%20click%20on%20%22Add%20aggregation%22%20for%20the%20second%20grouping.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-663156%22%20slang%3D%22en-US%22%3ERe%3A%20CONSOLIDATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-663156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EHi%2C%20but%20date%20column%20is%20vanished%20after%20doing%20this%20%3A(%3CBR%20%2F%3ENot%20sure%20why.%20%3A(%3CBR%20%2F%3EBut%2C%20thanks%20for%20opening%20power%20query%20for%20me.%20Totally%20new%20idea%20and%20I%20am%20exploring.%3CBR%20%2F%3E%3CBR%20%2F%3EBut%2C%20shall%20be%20grateful%20if%20you%20could%20help%20me%20to%20find%20out%20the%20root%20cause%20of%20missing%20%22date%22%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-663165%22%20slang%3D%22en-US%22%3ERe%3A%20CONSOLIDATION%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-663165%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302407%22%20target%3D%22_blank%22%3E%40mohsin91santono-_-1220%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECompare%20the%20code%20in%20my%20workbook%20with%20the%20code%20in%20your%20workbook.%20Maybe%20you%20can%20spot%20some%20differences.%3C%2FP%3E%3CP%3EThere%20are%20some%20steps%20in%20which%20I%20incorporated%20column%20renaming%20by%20editing%20the%20code.%3C%2FP%3E%3C%2FLINGO-BODY%3E
mohsin91santono-_-1220
Occasional 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.

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies