How to replace value row with null value under same column condition

[Anonymous]

Hi all,

Want to ask do we have any formulation to replace the completed qty with only 1 value based on the same order & document date

Current

What I expect. The goal is to sum all the completed qty for different order & document date.

Regards,

Nuha

[Anonymous]

Order	Document Date	Material	Material description	Completed	Base Unit of Measure	Material	Material description	Quantity	Base Unit of Measure
100001330945	10/4/2022	M0000006546	620 NO.744S 6MMX200M STD	1,379.000	ROL	M0000000628	NO.744S(NMM) 550X2000M	-1.000	ROL
100001330945	10/4/2022	M0000006546	620 NO.744S 6MMX200M STD	1,379.000	ROL	M0000000840	ABSCORE 6	-1,380.000	PC
100001330945	10/4/2022	M0000006546	620 NO.744S 6MMX200M STD	1,379.000	ROL	M0000002225	CARTON BOX R-5	-27.000	PC
100001330945	10/4/2022	M0000006546	620 NO.744S 6MMX200M STD	1,379.000	ROL	M0000002261	D/CUT PAD RR-4	-22.000	PC
100001330945	10/4/2022	M0000006546	620 NO.744S 6MMX200M STD	1,379.000	ROL	M0000002261	D/CUT PAD RR-4	-32.000	PC
100001330945	10/4/2022	M0000006546	620 NO.744S 6MMX200M STD	1,379.000	ROL	M0000002264	CREASE PAD RT-1	-27.000	PC
100001330945	10/4/2022	M0000006546	620 NO.744S 6MMX200M STD	1,379.000	ROL	M0000002283	LDPE PLASTIC BAG 560MM X 680MM X 0.03MM	-27.000	PC
100001330945	10/4/2022	M0000006546	620 NO.744S 6MMX200M STD	1,379.000	ROL	M0000002291	HDPE PLS SPACER 75MMX250MMX0.04MM	-1,406.000	PC
100001330945	10/4/2022	M0000006546	620 NO.744S 6MMX200M STD	1,379.000	ROL	M0000003636	LABEL (NITTO DENKO) 152MM X 102MM (NEW)	-31.000	PC
100001330945	10/4/2022	M0000006546	620 NO.744S 6MMX200M STD	1,379.000	ROL	M0000008234	000 NO.744S 550MMX2000M BAL STA	-440.000	M2
100001330945	10/4/2022	M0000006546	620 NO.744S 6MMX200M STD	1,379.000	ROL	M0000008234	000 NO.744S 550MMX2000M BAL STA	-275.000	M2

[v-jingzhang · Accepted solution]

Hi @Anonymous 

I would suggest using the Group By feature. Select the first four columns and click on "Group By" in Transform ribbon. For the new column, select Max operation on Completed column.  

Then you will have a distinct row for every combination of the first four columns with the distinct Completed Qty value.

If you want to keep all detailed information in other colulmns, you can duplicate the query first, then perform group by in the duplicated query. It will be easier to use this new table to calculate the sum. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Vijay_A_Verma]

Can you post the first picture as table? Refer to this - How to provide sample data in the Power BI Forum - https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Ideal will be to upload the file without confidential/sensitive data to a cloud storage service such as Onedrive/Google Drive/Dropbox/Box (Onedrive preferred) and share the link here.