Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to 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.
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.
@ngboss707 I don't think that is possible until you know that name/active will never be duplicated for the same IP.
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.
@ngboss707 can you share the pbix file, remove sensitive information before sharing.
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:
@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.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
88 | |
82 | |
64 | |
49 |
User | Count |
---|---|
125 | |
111 | |
88 | |
69 | |
66 |