cancel
Showing results for 
Search instead for 
Did you mean: 
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!






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!

 

 






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!






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!






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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.