Forum Discussion
Conditional column merge
Hi,
I have the following problem of merging columns.
I have a dataset with around 70 columns. It is a result from a merge with another dataset and some columns contain the same data. I would like to combine the columns which show the same data into one column in an easy and fast way.
There are some conditions, as one column has the role of a master.
So the condition to fill the new column is that the master column (say column A) should always be used, except the case, the column A is empty, then the column A* should be used.
Are there any tips and tricks to do to in an efficient way?
Example:
| Master column A | column A* | result column |
| 5 | null | 5 |
| 6 | 6 | 6 |
| 4 | 7 | 4 |
| null | 8 | 8 |
Previously, I used If else statements to achieve it but it seems to be not very efficient if I need to do that for 20 columns?
I read that some are using the unpivot and pivot functions, but I am not sure how to use it, since the sort of the column headers would not lead to any logic.
Any ideas are highly appreciated.
Thanks
Use Table.CombineColumns() to achieve your goal.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUVKK1YlWMgMyzMAsEyDLHMwCMiyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Master column A" = _t, #"Sub column A" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Master column A", Int64.Type}, {"Sub column A", Int64.Type}}), #"Combined Columns" = Table.CombineColumns(#"Changed Type", {"Master column A", "Sub column A"}, each if _{0} is null then _{1} else _{0}, "Combined column A") in #"Combined Columns"
4 Replies
Use Table.CombineColumns() to achieve your goal.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUVKK1YlWMgMyzMAsEyDLHMwCMiyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Master column A" = _t, #"Sub column A" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Master column A", Int64.Type}, {"Sub column A", Int64.Type}}), #"Combined Columns" = Table.CombineColumns(#"Changed Type", {"Master column A", "Sub column A"}, each if _{0} is null then _{1} else _{0}, "Combined column A") in #"Combined Columns"Hi tmul ,
In PQ editor you could try something like this.
List.First(List.Select({[Master], [Column1], [Column2], [Column3],[Column4]}, each _ <> null))Sorry tmul - I misread your post and thought you needed a COALESCE type solution for ~70 columns. Below doesn't seem like what you are looking for. I mostly decided to 'try' to improve the formula when there were more than just a few columns (e.g. ~70).
Payeras_BI - Try this instead of declaring all the column names:
= Table.AddColumn(#"Changed Type", "Custom", each List.First(List.RemoveFirstN(Record.ToList(_), each _ = null),null))and also the opposite:
= Table.AddColumn(#"Changed Type", "Custom", each List.Last(List.RemoveLastN(Record.ToList(_), each _ = null),null))
