Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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 dateDomain 1Domain 2ViewsLikes
1-2-2022Work 105
1-3-2022Care 136
1-4-2022LivingCare149
1-5-2022WorkCare1611

 

What I want to create is this:

DomainNumber of articlesViewsLikes
Work22616
Care34326
Living1149

 

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!

1 ACCEPTED SOLUTION

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:

AlexisOlson_0-1648068491720.png

and an Articles table like this:

AlexisOlson_1-1648068529588.png

And have them related like this:

AlexisOlson_2-1648068661185.png

View solution in original post

8 REPLIES 8
hashtag_pete
Helper V
Helper V

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

Anonymous
Not applicable

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!

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:

AlexisOlson_0-1648068491720.png

and an Articles table like this:

AlexisOlson_1-1648068529588.png

And have them related like this:

AlexisOlson_2-1648068661185.png

Anonymous
Not applicable

Thank you Alexis, this is the approach that works best!

HotChilli
Super User
Super User

What's the logic behind 'Living' getting 1 in the Number Of articles?

Anonymous
Not applicable

My mistake, there is no logic. I have been staring at these things for way too long 😂 Let me adjust my original post! Thanks for letting me know.

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...

Anonymous
Not applicable

yes you are correct, I adjusted my mistake in my original post so you can disregard HotChilli's question 😊

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.