Forum Discussion
Merging Columns and replacing Null values
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?
Hi Anonymous ,
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
2 Replies
Hi Anonymous ,
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
List.Max(Record.ToList(_))
