Forum Discussion
Creating table with categories from 2 columns in 1 column
Hi people,
I am creating a content performance report. My company posts content on LinkedIn, and assigns a domain to each article. However, one article can have multiple domains. In the datafile I got from the Marketing department, both domains are listed seperately in 2 columns. I want to add the performance of all articles that share the same domain, for which I have to look at two columns.
So I have got a table that looks like this:
| Post date | Domain 1 | Domain 2 | Views | Likes |
| 1-2-2022 | Work | 10 | 5 | |
| 1-3-2022 | Care | 13 | 6 | |
| 1-4-2022 | Living | Care | 14 | 9 |
| 1-5-2022 | Work | Care | 16 | 11 |
What I want to create is this:
| Domain | Number of articles | Views | Likes |
| Work | 2 | 26 | 16 |
| Care | 3 | 43 | 26 |
| Living | 1 | 14 | 9 |
I have no clue how to do this... I have looked into DISTINCT(UNION(VALUES..., GROUPBY, SUMMARIZE... Nothing seems to work and I am out of ideas.
Help would be very much appreciated! Thanks!
Unpivoting as hashtag_pete suggests is a good idea but it does bring complications. One approach would be to do this unpivot in a separate table so that you have a Domains table like this:
and an Articles table like this:
And have them related like this:
8 Replies
- HotChilli
Community Champion
What's the logic behind 'Living' getting 1 in the Number Of articles?
- hashtag_pete
Helper V
I thought this is the count of how many times was this category addressed?
In your example, Living exists once, Work twice and Care three times...
- hashtag_pete
Hello Anonymous ,
you need to go to Power Query and select the columns with the domains, in your example "Domain 1" and "Domain 2", then go to the Tab "Transform" and make "Unpivot Columns".
You can then for example delete the empty ones or load it all to Power BI Desktop, in order to do further calculations there.
If this helps, please give kudos and accept as solution.
Best
hashtag_pete
Hi Pete,
Would that imply that I first have to duplicate the query? Because when I unpivot, those articles that have two domains show up as two different rows. For some other measures I have written DAX that for example averages the number of views:
Views(average) = AVERAGE(LinkedIn[Views])Since, after unpivoting, one article can show up in two rows, I guess those measures do not give me the correct value anymore, right? I could solve this by duplicating the query and doing your trick in that query. Or could you think of a better way? Thanks!
- AlexisOlson
Super User
Unpivoting as hashtag_pete suggests is a good idea but it does bring complications. One approach would be to do this unpivot in a separate table so that you have a Domains table like this:
and an Articles table like this:
And have them related like this:
