Alternate for COALESCE in Power Query

[cramkumar86]

Hi,

I have a report that connects to Impala and it is super slow as it fetches close to 36 million rows. Some of the transformations do happen within Power BI and I am trying to find ways to improve the efficiency of data load.

Although I am not 100% sure, I have a feeling that COALESCE operator that I use to find the non-blank values between columns is inefficient, as this is performed on 36 million rows.

I have 6 steps created like below to achieve this. In the first 5 steps, Columns 1 to 5 are created using COALESCE operator. Once they are created, columns A to J will be removed in the last step.

Col1 <- col A ? col B

Col2 <- col C ? col D

Col3 <- col E ? col F

Col4 <- col G ? col H

Col5 <- col I ? col J

Remove Cols A to J.

Are there options to make it more efficient?

Regards,

Ram

[amitchandak · Accepted solution]

@cramkumar86 , You can use replace with null and value, which will use Table.ReplaceValue