May 30 2019 12:14 AM
Hello there,
My attached picture is self explanatory.
Please help me to provide a good formula.
Thanks in advance
May 30 2019 01:00 AM - edited May 30 2019 01:01 AM
May 30 2019 01:00 AM - edited May 30 2019 01:01 AM
@Celia_Alves
Hello Celia,
Could you please help?
I am back again for your advice. :)
May 30 2019 02:54 AM
May 30 2019 04:21 AM
May 30 2019 04:50 AM
@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.
May 30 2019 06:02 AM
SolutionAssign 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.
Jun 01 2019 11:42 PM
@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.
Jun 02 2019 01:11 AM
Value - Sum - Value refer to the fields New column name - operation - Column.
And then click on "Add aggregation" for the second grouping.
Jun 02 2019 02:55 AM
@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.
Jun 02 2019 03:27 AM
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.
May 30 2019 06:02 AM
SolutionAssign 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.