Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thank you all for the help! I looked at all the proposed solutions and based on the test, the solution from @CNENFRNL fits best, although if it means I need to write this line of code with the Table.CombineColumns around 20 times...
But thanks and have a great day!
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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))
Proud to be a Super User!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
95 | |
91 | |
35 | |
29 |