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
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies