Combine Row values when there are null column values for a few rows

[Shivakks]

Hi all,

I have a scenario below where I need to combine the rows in column when the remaining rows have null values.

I am new to Power BI, please help me with this.

[Ahmedx]

I think you need to do a Fill Down on each column

to know how to do this watch my video

https://1drv.ms/v/s!AiUZ0Ws7G26RjEeSyXM5Oas0EBQ4?e=awhYOz

[Shivakks]

@Ahmedx I have done a fill-down of all columns. Whats the next step  to merge the rows in column Transaciton Description.

[Ahmedx]

show the result you want
and you can insert a table here, I can’t copy data from the picture

[Shivakks]

Example Row 9,10,11 of Transaciton Description is what I want to merge.

DATE	VALUE DATE	Transaction Description	DESCRIPTION
4/3/2023	4/3/2023	Reverse Charges	null
4/3/2023	4/3/2023	Reverse Charges	null
4/3/2023	4/3/2023	Reverse Charges	null
4/3/2023	4/3/2023	Reverse Charges	null
4/3/2023	4/3/2023	Reverse Charges	null
4/3/2023	4/3/2023	Reverse Charges	null
4/3/2023	4/3/2023	Reverse Charges	null
4/3/2023	4/3/2023	Reverse Charges	null
4/5/2023	4/5/2023	IPI Transfer	FAB Ref: CI133RHWEDN70075 O
4/5/2023	4/5/2023	utward IPI Payment BeneIBAN: AE6603300000	FAB Ref: CI133RHWEDN70075 O
4/5/2023	4/5/2023	19100673360	FAB Ref: CI133RHWEDN70075 O
4/6/2023	4/6/2023	IPI Transfer	FAB Ref: CI233S8OP2L71278 O
4/6/2023	4/6/2023	utward IPI Payment BeneIBAN: AE5204000001	FAB Ref: CI233S8OP2L71278 O
4/6/2023	4/6/2023	92892504001	FAB Ref: CI233S8OP2L71278 O
4/6/2023	4/6/2023	Inward IPI Payment	IPI Ref: IPI23040602W
4/6/2023	4/6/2023	THLS Remitter Info: SHIVARAM KULANDAISAMY	IPI Ref: IPI23040602W
4/6/2023	4/6/2023	Sender: NRAKAEAK Value Date: 06-04-2023	IPI Ref: IPI23040602W
4/6/2023	4/6/2023	Trf Ccy: AED Trf Amt: 2000.00 Pay Dtls: /	IPI Ref: IPI23040602W
4/6/2023	4/6/2023	REF/AED POP: FAM Ord Inst: NATIONAL BANK	IPI Ref: IPI23040602W
4/6/2023	4/6/2023	OF RAS AL-KHAIMAH, THE	IPI Ref: IPI23040602W
4/6/2023	4/6/2023	IPI Transfer	FAB Ref: CI933SB2U5871727 O
4/6/2023	4/6/2023	utward IPI Payment BeneIBAN: AE8700300119	FAB Ref: CI933SB2U5871727 O
4/6/2023	4/6/2023	62184920001	FAB Ref: CI933SB2U5871727 O
4/11/2023	4/11/2023	IPI Transfer	FAB Ref: CI933YWKY1K08684 O
4/11/2023	4/11/2023	utward IPI Payment BeneIBAN: AE6603300000	FAB Ref: CI933YWKY1K08684 O
4/11/2023	4/11/2023	19100673360	FAB Ref: CI933YWKY1K08684 O
4/13/2023	4/13/2023	Inward Telex Transfer	Ref: AE1RCXT231030
4/13/2023	4/13/2023	3DQ, Remitter Info: EMERSON FZE, Sender:	Ref: AE1RCXT231030
4/13/2023	4/13/2023	Balance carried forward	Ref: AE1RCXT231030
1/2/2023	1/2/2023	Inward IPI Payment	IPI Ref: IPI23010202E
1/2/2023	1/2/2023	I6RK Remitter Info: SURESH TADAVARTHI N S	IPI Ref: IPI23010202E
1/2/2023	1/2/2023	TADAVARTHI Sender: ADCBAEAA Value Date:	IPI Ref: IPI23010202E
1/2/2023	1/2/2023	02-01-2023 Trf Ccy: AED Trf Amt: 12500.00	IPI Ref: IPI23010202E
1/2/2023	1/2/2023	Pay Dtls: /REF/Family Support POP: FAM O	IPI Ref: IPI23010202E
1/2/2023	1/2/2023	rd Inst: ABU DHABI COMMERCIAL BANK	IPI Ref: IPI23010202E
1/2/2023	1/2/2023	IPI Transfer	FAB Ref: CI23EIY4P1F49310 O
1/2/2023	1/2/2023	utward IPI Payment BeneIBAN: AE3604000002	FAB Ref: CI23EIY4P1F49310 O
1/2/2023	1/2/2023	92941243001	FAB Ref: CI23EIY4P1F49310 O
1/2/2023	1/2/2023	Reverse Charges	FAB Ref: CI23EIY4P1F49310 O
1/2/2023	1/2/2023	Reverse Charges	FAB Ref: CI23EIY4P1F49310 O
1/2/2023	1/2/2023	Reverse Charges	FAB Ref: CI23EIY4P1F49310 O
1/2/2023	1/2/2023	Reverse Charges	FAB Ref: CI23EIY4P1F49310 O
1/2/2023	1/2/2023	Reverse Charges	FAB Ref: CI23EIY4P1F49310 O
1/2/2023	1/2/2023	Reverse Charges	FAB Ref: CI23EIY4P1F49310 O
1/3/2023	1/3/2023	IPI Transfer	FAB Ref: CI33EL2E6MJ53497 O
1/3/2023	1/3/2023	utward IPI Payment BeneIBAN: AE3604000002	FAB Ref: CI33EL2E6MJ53497 O
1/3/2023	1/3/2023	92941243001	FAB Ref: CI33EL2E6MJ53497 O
1/3/2023	1/3/2023	IPI Transfer	FAB Ref: CI43EL3CIUA53571 O
1/3/2023	1/3/2023	utward IPI Payment BeneIBAN: AE5204000001	FAB Ref: CI43EL3CIUA53571 O
1/3/2023	1/3/2023	92892504001	FAB Ref: CI43EL3CIUA53571 O
1/3/2023	1/3/2023	IPI Transfer	FAB Ref: CI93EL3DS1753667 O
1/3/2023	1/3/2023	utward IPI Payment BeneIBAN: AE3902000000	FAB Ref: CI93EL3DS1753667 O
1/3/2023	1/3/2023	41187683001	FAB Ref: CI93EL3DS1753667 O
1/3/2023	1/3/2023	IPI Transfer	FAB Ref: CI43ELAR4NP54458 O
1/3/2023	1/3/2023	utward IPI Payment BeneIBAN: AE1404000000	FAB Ref: CI43ELAR4NP54458 O
1/3/2023	1/3/2023	33417116001	FAB Ref: CI43ELAR4NP54458 O
1/3/2023	1/3/2023	IPI Transfer	FAB Ref: CI13EKMXRPX54656 O
1/3/2023	1/3/2023	utward IPI Payment BeneIBAN: AE3604000002	FAB Ref: CI13EKMXRPX54656 O
1/3/2023	1/3/2023	Balance carried forward	FAB Ref: CI13EKMXRPX54656 O

[Anonymous · Accepted solution]

Hi @Shivakks 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZfbbtpAEIZfZZRrKHvy+nC3PiC7BkNtSEKjXFjJ0kYCpzKmbd6+a0oDCthgo0i9CFdrxH6/Z2fm3+Hu7or1aI8gQq86+8tY/pT5SoLzPc2/yZX6JlsvFlf3nY8N77ZB2214XQbjACZ5mq3mMlePfWFDLOcWOAGmNPZvPDfSEdI1GNVA1sWvNH+EkjVOX5YyK8CWmQxsEVkgPM4Rpaj8tFbAJkaI65Ty8xh8x+CnQyWUJsZoTAY6JrpRCzkRqkYQ20SKWyuYxDCJVmLaM4Ls7Vtu49+g1IJQJcARuamBTPxBojYsn4pC5hBk82cLEj+4FrEYQjgdiMgVQSKGsxbsRGaPMrcgikUoPBHCdbpYS3DTQlqAeBex7l7WGr11Pgfn4aVMhgvlg1gWFhCVkk8IlacBbrFYWdBrwY69fq/EjkdjC/rqFEblIWcrJRCJSTCKxABUIYQt0KM+xCIBMeiGvgiGwu/AxPdagKqr3FT1Y5OpZuhYJ/olVW6oZlMNjbHZWoETbDCTHHZKBQPjHWS3rg12dhPOcIgMbrATmJb+1Uij0sGqKHv2vltvG3siF/L3fuQblvBw7NxOCMUqO7UY6n7pvG1tb+jFySiC/levA9v+bE6200WaPUh4SPP8ST7C/Dkv37gapA7o9Yh2y3MMDCP1Y68OwuPwwMCmsZf4MBGuUEY28QOIIGnB3gP88zLhOrbyMrHvZS3IiHQR3rgfHPcyrG6H0sxasPfsr/Syfrp8WrxAsv7x4zkv9mytBfrVCYU9BdcXdgDOaKhqygkqXfF0BmuubC+YsTHuM1PV0rZrjkNOdLdqyL9XNmmtYBKTYcLokSv7bMbh5PbB+V84dMehp0uTUm9APD78rFFm6rWQlqXZQKGyNBswqkNlCkKdYCo0qun4klCrBucGCpWDcwNG3UChIG6CdY1yfllWTUTQ0XHibAWGsaFz4zCrDRj1WRUxi8YaY5pxSaiYbbOKWitQyrCOMT+a1TMZdX9yvXB4G49vNcY1/h692kChenaq5d3/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, #"VALUE DATE" = _t, #"Transaction Description" = _t, DESCRIPTION = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"VALUE DATE", type date}, {"Transaction Description", type text}, {"DESCRIPTION", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.Distinct(Table.SelectRows(#"Changed Type",(x)=>x[DESCRIPTION]=[DESCRIPTION])[Transaction Description]),"|")),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Custom"})
in
    #"Removed Duplicates"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Based on the data that you have shared, show the expected result.