Need to Transform Data In Power BI

[RajeshZZ424]

I've Imported Data from Databricks to Power BI. The data seems to look like Below.  

batchid	sampleid	REPLICATEID	CreateDate	Thickness	Dry	Wet	Mixed
B-20231006-0039	S-20231009-0179	1	2023-10-09	20.85	NULL	NULL	NULL
B-20231006-0039	S-20231009-0179	1	2023-10-09	NULL	44.3	NULL	NULL
B-20231006-0039	S-20231009-0179	1	2023-10-09	NULL	NULL	NULL	41.85
B-20231006-0039	S-20231009-0179	1	2023-10-09	NULL	NULL	33.85	NULL
B-20231006-0039	S-20231009-0179	1	2023-10-09	21.57	NULL	NULL	NULL
B-20231006-0039	S-20231009-0179	2	2023-10-09	NULL	45.09	NULL	NULL
B-20231006-0039	S-20231009-0179	2	2023-10-09	22.09	NULL	NULL	NULL
B-20231006-0039	S-20231009-0179	2	2023-10-09	NULL	NULL	34.23	NULL
B-20231006-0039	S-20231009-0179	2	2023-10-09	NULL	NULL	NULL	42.44

If you look at the Batchid, Sample_id, createdate are same Data. it deferred with replicateID 1 and 2 . Here each value in Thickness, dry, wet, mixed is taking one for each valuea and getting remaining as NULL. I want to decrease rows. 

I want to show data in below format as below to reduce rows and for better report looking.

batchid	sampleid	REPLICATEID	CreateDate	Thickness	Dry	Wet	Mixed
B-20231006-0039	S-20231009-0179	1	2023-10-09	20.85	44.3	33.85	41.85
B-20231006-0039	S-20231009-0179	1	2023-10-09	21.57	NULL	NULL	NULL
B-20231006-0039	S-20231009-0179	2	2023-10-09	22.09	45.09	34.23	42.44

Any help on this is appreciated 

[Ahmedx]

what should be the result? such

[RajeshZZ424]

In Null value must be 45.09  you pointed.

[Ahmedx]

explain why it should be this way and on what basis

[RajeshZZ424]

If you see last three four rows data 

B-20231006-0039	S-20231009-0179	2	2023-10-09	NULL	45.09	NULL	NULL
B-20231006-0039	S-20231009-0179	2	2023-10-09	22.09	NULL	NULL	NULL
B-20231006-0039	S-20231009-0179	2	2023-10-09	NULL	NULL	34.23	NULL
B-20231006-0039	S-20231009-0179	2	2023-10-09	NULL	NULL	NULL	42.44

I need out like this because 

B-20231006-0039

S-20231009-0179

2

2023-10-09

22.09

45.09

34.23

42.44

I will reduce the number of rows 

[RajeshZZ424]

batch ID - 

B-20231006-0039

SampleID - S-20231009-0179

ReplicateID - 2
Createdate- 2023-10-09

[Ahmedx · Accepted solution]

pls try this

[Idrissshatila]

Hello @RajeshZZ424 ,

you can use group by in power query.

check it out https://learn.microsoft.com/en-us/power-query/group-by