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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ngboss707
New Member

Pivot with multiple values scatered thorugh rows + nulls (Powerquery)

I'm fairly new to PQ and I'm struggling for some days with this problem. The data to transform was a mess with multiple cells and columns containing line breaks without any pattern, but I already cleared the data down to one column of key values filled (IP) and some attributes extracted to columns that can have one value per cell, but can be empty or have multiple values for each key:

 

IPNameActiveSitesUsers
1PC1 XYZUser2
1 Y User1
1  ABC 
2 N  
3PC3   
3 Y  
3   User3
3PC3 ABC 

 

I really appreciate if someone can help to achieve something like the example below in Power Query as I'm using Excel (don't need graphics or super metrics), but I can migrate to BI if it's only possible in visualization. Also doesn't matter if the columns are merged with multiple rows to accomodate multiple attributes in other colums or if the attributes are concatenated into a single cell like below (atribute sorting also optional):

 

IPNameActiveSitesUsers
1PC1Y

ABC

XYZ

User1

User2

2 N  
3PC3YABCUser3

 

Thanks in advance.

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@ngboss707 it will be easier if you unpivot this:

 

Transform data -> select IP -> right click -> Unpivot other columns

 

it will create two column, attribute and value, rename as you see fit, close and apply

 

Use matrix visual, put IP on rows, attribute on column and value on values and that will do it.

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

@ngboss707 I don't think that is possible until you know that name/active will never be duplicated for the same IP.

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@ngboss707 can you share the pbix file, remove sensitive information before sharing.

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi parry, I think I got it by looking on how to use custom measures from other forum posts here. Just a question: is it possible to make a row for each distinct value in each column, with the IP column and other columns merged if there is only a single value in that column? I know I said before that it doesn't matter how the final result is presented, but I found it hard to filter the values in others columns because of the concatenations, something like this:

 

samplexls.PNG

File: Sample pbix 

parry2k
Super User
Super User

@ngboss707 it will be easier if you unpivot this:

 

Transform data -> select IP -> right click -> Unpivot other columns

 

it will create two column, attribute and value, rename as you see fit, close and apply

 

Use matrix visual, put IP on rows, attribute on column and value on values and that will do it.

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi parry2k, thanks for the help! This formatted the table correctly, although all lines are appearing blank for me.

 

I changed some settings in the values field, but I can't find how to display the 'values' text instead of totals or counts. I chose to create a quick text measure to concat the fields but the result still is the same. When expanding the value cells I can see that it's also counting the blanks and dupes.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.