How to Merge two rows with nulls and data (half of rows is filled with data and half with nulls)

[kkg1991]

Hello Everyone,

Is there any way to merge two rows which having nulls and data in both rows

for ex:

rows looks like this

code       name             yr            values                            code                  name               lastyr             lastyrvalues

xyz         abc               2018         123                               null                       null               null                  null

null         null              null            null                             xyz                        abc                 2017               345

I want to merge those two columns into one single column and the final row I'm looking for is

code          name        yr            values                  code         name      lastyr            lastyrvalues

xyz            abc           2018       123                     xyz              abc           2017             345

Is there any way to get the result like this in Power BI Query Edit Window I tried with transposing entire table and merging two columns with a delimiter and pivoting and unpoviting but didn't get exact outcome what I'm looking for.

any help is appreciated.

Thanks   

[v-danhe-msft · Accepted solution]

Hi @kkg1991,

Based on my test, you can refer to below steps in Query Editor:

1.Use the “Transpose” function to transpose your data.

2.Use the “Custom Column” function to merge the Column2 and the Column3.

Custom=[Column2]&[Column3]

3.Remove the Column2 and the Column3.

4.Transpose your data again and use the "Use First Row as Headers" function to set your headers. Now you can get the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/8ch5acxfbta3epx/How%20to%20Merge%20two%20rows%20with%20nulls%20and%20data....

Regards,

Daniel He

[kkg1991]

Thanks Daniel for your help. 

[v-danhe-msft]

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful reply as Answered?