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

[ngboss707]

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:

IP	Name	Active	Sites	Users
1	PC1		XYZ	User2
1		Y		User1
1			ABC
2		N
3	PC3
3		Y
3				User3
3	PC3		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):

IP	Name	Active	Sites	Users
1	PC1	Y	ABC XYZ	User1 User2
2		N
3	PC3	Y	ABC	User3

Thanks in advance.

[parry2k]

@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  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!

[parry2k]

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

✨ Follow us on LinkedIn and  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!

[ngboss707]

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:

File: Sample pbix 

[parry2k · Accepted solution]

@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  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!

[ngboss707]

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.