Merging Columns and replacing Null values

[DSwezey]

Hi all, 

Brand new to Power BI so bear with me.

I just merged 4 queries into one generating quite a bit of data. 

These were merged based on the 1st column in each query. 

What I am looking to do is merge these 4 columns into one, but my issue is with the null values. Each column is a different year and each year has slightly different data in each row. How can I go about merging all 4 columns and replacing the null values with the missing data from respective columns?

[BA_Pete · Accepted solution]

Hi @DSwezey ,

Not sure if this fully meets your requirements (it should be noted that in your example [BS 2020] actually holds a complete dataset), but you could try adding a new column with the following calculation:

List.Max({[BS 2018], [BS 2019], [BS 2020], [BS 2021]})

Obviously this isn't particularly dynamic and will break if/when you change the years but, for a one-off operation, should work ok.

Pete

[CNENFRNL]

List.Max(Record.ToList(_))