Forum Discussion
Pivoted table with multiple values on one column
I'm facing a problem pivoting a table.
I have issues with 3 columns (after pivoting) which fields have more than one value.
The problem is that these columns have hundreds of fields with their right values but all the other columns for this value are filled with NULL.
It makes sense as those fields have no data.
So i have no idea on how to solve this.
8 Replies
mcflurry Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.All my data is like this:
I have +100k rows with the values in rows instead of columns.
So in order to get it "correctly" i group the data bye INSIGHT_ATTRIBUTE_NAME, then add an Index Column and then pivot it so i get a colum for each of these values.
But as for columns with more than one value (like CPUs, Disk and Network Interfaces) it creates a lot of rows for these (some servers have 10 network interfaces, 32 CPUs, and 15 Disks):
Just like this, CPUs, Network Interfaces and Disks columns have hundreds of values surround but everything else "null".
So, the point is:
Pivoting a column when there are multiple values creates hundreds of NULL fields because it doesn't merge the values... would it be possible to concatenate these? Merge???
Without further clarification and sample data I can only guess...
- Take you 100K table
- Do a group by on all columns except the insight attribute columns
- selecting 'keep all rows'
- Name the aggregate column [Attribute Rows]
- Add an new column with the following formula
- = Table.AddColumn(#"Grouped Rows", "Attributes", each Text.Combine([Attribute Rows][INSIGHT_ATTRIBUTE_NAME], "|"))
Combine in the same cell based on other field (server name, object key... i have a few fields which can be used for this) separataed by a ||
If you want us to help you, it is mandantory to privide sample data in usable format (if yo don't know how - read note below my post) and expected result based on sample data (at least screenshot)
