Pivoted table with multiple values on one column

[mcflurry]

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.

[dufoq3]

Hi @mcflurry, could you provide expected result based on this data please?

[Keezz]

Without further clarification and sample data I can only guess...

1. Take you 100K table
2. Do a group by on all columns except the insight attribute columns
   • selecting 'keep all rows'
   • Name the aggregate  column [Attribute Rows]
3. Add an new column with the following formula
   • = Table.AddColumn(#"Grouped Rows", "Attributes", each Text.Combine([Attribute Rows][INSIGHT_ATTRIBUTE_NAME], "|"))

[mcflurry]

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

[dufoq3]

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)

[mcflurry]

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???

[mcflurry]

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".

[Anonymous]

Hi @mcflurry 

As @dufoq3  mentioned, you need to share some sample data, the original data you have offered cannot describe it clearly, can you provide some sample data and the output you want so that can provide more suggestion for you.

Best Regards!

Yolo Zhu

[Greg_Deckler]

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