PIVOT ERRORS in Power BI I How to FIX IT

How to Power BI2021-02-21

pivoting power bi#power bi pivoting#pivot error power bi#power query#m power bi#pivot transformation#pivot power bi#power bi pivot#power query help#power query error#error power query#pivot error in power query#power bi m#learn m#m in power bi#pivoting in power bi#resolve pivot error#power bi#power bi tutorials for beginners#power bi desktop

12K views|3 years ago

💫 Short Summary

This tutorial in the 'How to Power BI' series by Bosé will help you understand the reasons why a pivot operation in Power BI may fail and how to overcome those issues using Power Query transformations. Bosé demonstrates two methods to solve the problem and provides a comprehensive overview of the process with practical examples.

✨ Highlights

📊 Transcript

✦

This section demonstrates how to pivot a table in Power BI and the potential issue that can arise if the table is not set up correctly.

00:00

In an ideal case scenario, the data set has three columns: employee names, attributes, and corresponding values.

Pivoting the table transforms rows into columns based on the attribute column.

A small change to the data set, removing the employee names column, causes the pivot operation to fail due to too many elements in the enumeration.

✦

A third column, 'Entity', is added to map the attributes to the employees, and a conditional column is used to display the employee names.

02:29

The 'Entity' column is added by undoing the last step and then adding a conditional column for the employee names.

Null values in the employee names column are filled down.

With the three required columns (Attribute, Value, Entity) in place, the pivot operation can be successfully executed.

✦

Another approach to solve the pivot issue is demonstrated by adding an index number for each entity using the 'Group By' function.

04:46

Nested tables are created for each attribute with its corresponding values for different entities.

An index column is added within each table to map the attributes to the entities.

The index column is expanded, and the value in the index is brought over.

The pivot operation is performed again, and the attribute column is selected to complete the process.

✦

The process of combining multiple values for the same entity using the 'Group By' function and resolving errors when taking the sum of text values.

07:15

Grouping by employee and attribute to ensure each employee has only one instance of each attribute.

Modifying the formula by transforming list.sum into text.combine and adding a comma between values.

Changing the data type to text to resolve errors and successfully pivoting the data.

✦

A potential issue when pivoting data is having no values in the attribute column, which can be resolved by removing empty cells before pivoting.

09:31

An error is shown when trying to pivot the data with a null value in the attribute column.

The issue can be fixed by removing empty cells in the attribute column before pivoting.

00:00 let's pivot this table so i'm going to

00:02 select the column that i want to pivot

00:04 go to transform pivot column and then

00:07 here we can choose

00:09 the value column go to advanced options

00:11 and i do not want to aggregate

00:14 and arrow

00:18 do you know how to fix this

00:20[Applause]

00:28 welcome to how to power bi my name is

00:30 boss and if this is the first time for

00:31 you visiting this channel then make sure

00:33 to hit that subscribe button

00:35 if you want to stay up to date on

00:36 everything power bi related

00:38 now let's have a look at why that pivot

00:42 didn't work now let's first have a look

00:44 at an ideal case scenario for pivoting

00:47 so in this data set i have three columns

00:49 the first column contains

00:51 the employee names then for these

00:54 employees we

00:55 are storing the information for

00:56 different attributes for example

00:58 the office department starting date

01:01 and then we have the corresponding

01:03 values in the third column

01:04 so now i want to transform this table

01:06 and turn rows

01:07 into columns on the basis of this

01:10 attribute column okay so

01:12 for each attribute i want to have a

01:14 separate column

01:15 now we can do this with the pivot

01:18 transformation so i go

01:19 to transform pivot column and here

01:22 the column that we want to have for the

01:25 values

01:26 that's the value column here and then we

01:29 can go to advanced options

01:31 and then we do not want to aggregate the

01:33 values but we want to

01:34 show the actual values that we have

01:36 inside of the value code

01:38 and that gives me the pivoted result

01:40 however let's now make

01:42 a small change to that underlying data

01:44 set so i'm going to

01:46 take out one column and now we only have

01:49 the attribute column

01:50 and value column and the names of the

01:52 employees well

01:54 they turned into an attribute right so

01:56 we do not have a separate

01:58 column anymore for the applicant names

02:00 and i'm going to do

02:01 exactly the same so i want to take the

02:04 attribute column

02:05 and then go here to transform pivot

02:08 column

02:09 then the values column well that's that

02:11 second column over there

02:13 values and we do not want to aggregate

02:17 and now it doesn't work anymore and the

02:19 error that we get

02:20 is that there were too many elements in

02:22 the enumeration to complete the

02:24 operation

02:26 okay now the reason is in that first

02:28 data set

02:29 we have that extra column for

02:32 the amplitude names the entity column

02:34 that allows us to

02:36 map the different attributes to an

02:38 entity and that is what

02:39 power query needs so to make that second

02:42 example

02:43 work we have to add a third column so

02:46 let's do this so

02:48 i'm going to go back and undo the last

02:50 step

02:51 i'm going to add a conditional column

02:55 and this new column is then for the

02:58 employee name

03:01 and we can say if the attribute equals

03:05 name then we want to

03:08 show the corresponding value all right

03:10 so let's see

03:11 that gives us the amplitude names inside

03:14 of the

03:15 empty column however we still have some

03:17 nulls so let's fill it down

03:19 let's select the column and then go to

03:21 transform

03:22 and here we can say fill down okay so

03:26 now we have three columns let's give it

03:29 another shot so i'm going to

03:30 select the attribute column then click

03:33 on pivot column

03:35 and over here we set it to do not

03:38 aggregate

03:40 and we fix it you see so keep that in

03:43 mind you need to have

03:45 three columns otherwise it will not work

03:47 it needs to be able

03:48 to map the attributes to the entity now

03:51 that was just one approach

03:52 let me now also show you a second way of

03:54 how you could solve it

03:56 with an index number for the entity okay

03:59 so we can go here

04:00 to the home tab or transform and choose

04:04 group by and here we can switch to

04:07 advanced and we want to group by the

04:08 attribute

04:10 and here we want to keep all of the rows

04:13 therefore the new column name we can

04:15 type in value

04:17 and okay so now here we basically just

04:21 have nested tables as you see per

04:24 attribute we have the corresponding

04:27 values for peter and sam

04:28 okay so now i'm going to add

04:32 an index number for

04:35 the first entity and the second entity

04:37 okay so within each table

04:39 and the way to do that is to go here to

04:42 add column

04:43 custom column and this is going to be

04:46 our index column and the function that

04:50 we can use

04:50 is table dot add index column

04:54 okay so here we can take the value

04:58 and the new column name is going to be

05:02 index and we want to have increments of

05:05 one so you see that gives me a column

05:08 with again asset tables and

05:10 now we have the attribute and values now

05:12 every time in this case we have two

05:14 so one for beta two for seven and for

05:17 the next one

05:17 for the next attribute we also have the

05:19 index one

05:20 two so we have a mapping of the

05:22 attributes to the corresponding entity

05:25 that's all we need so now we can expand

05:28 the index column

05:29 now we want to bring over the value in

05:31 the index

05:33 now the value column here we don't need

05:36 all right and now we are ready

05:40 to do the pivot again so i can click on

05:42 the attribute column

05:44 transform pivot column and then here we

05:47 have don't aggregate and click on ok

05:52 and that also solves it now let me show

05:54 you a second roadblock that you can run

05:56 into and that is

05:57 having duplicates in the attribute

06:00 column for the same

06:01 entity for example here i added now two

06:04 rows

06:05 and these two rows are for sam

06:08 and you see i added two times

06:11 specialization because

06:13 sam and employee can have multiple

06:15 specializations

06:16 for example database administration and

06:19 platform

06:19 integration okay now i would like to

06:22 pivot this table just like before

06:24 so i'm going to select the attribute

06:26 column transform paste column

06:28 now here i can take the value field

06:31 again and i don't want to

06:32 aggregate you see we have

06:36 an error it almost worked however we

06:38 have now this

06:39 new column for the specialization

06:40 attribute and

06:42 there we have the error there were too

06:44 many elements in the enumeration to

06:46 complete the operation

06:48 same error as before and that is because

06:50 we have now

06:52 two times attribute there with

06:55 two different values okay and it cannot

06:58 show

06:59 both of them over here in one cell so

07:02 how could we

07:03 solve this now let's go one step back so

07:05 go here to the navigation step

07:08 now to fix this we just have to make

07:09 sure that we just have one time

07:12 each attribute for each entity that's

07:14 for each

07:15 employee so if we could just combine

07:18 platform integration

07:19 with database administration there so

07:22 that we have one line

07:23 then the pivot will work and the

07:26 transformation

07:27 that allows us to do that is the group

07:29 buy all right so i go

07:31 again to the home tab click here on

07:33 group by

07:34 and here we want to switch to advanced

07:37 and then we are going to group

07:38 by the employee and also the attribute

07:42 and then we can create our value column

07:45 with the operation

07:46 sum and we want to take the sum of the

07:48 value

07:50 i'll click okay i see it gives us a

07:52 whole list of errors and that's

07:54 just because we cannot take the sum of

07:56 text values of course

07:57 now but we can take the formula and

08:00 tweak it a little bit so that it works

08:02 so we need to make sure that

08:04 you can see the formula bar if you don't

08:06 see it just go to view

08:08 formula bar and then it should show up

08:11 all right and now we can go here to the

08:13 formula

08:14 and transform list.sum into the

08:17 following function

08:19 text dot combine

08:25 and now it almost works now let me make

08:28 this

08:28 come a little bit wider you see there we

08:31 have database admin

08:32 platform integration now i would like to

08:35 have a comma in between

08:36 so just go to the formula and add

08:38 another

08:40 argument with the comma in between

08:43 quotation marks

08:45 okay so that's fixed and what about

08:47 these other errors well here

08:49 we cannot convert the date value to type

08:51 text all right so

08:53 we need to go one step before and just

08:57 change the data type here to text

09:00 all right so now that it's taxed then we

09:02 can group it

09:03 you see no more errors and then we can

09:06 do our pivot again

09:07 so i'm going to click here on the pivot

09:09 column step you see

09:10 we do not have any hours anymore and we

09:13 nicely

09:13 have there our two values database admin

09:17 and platform integration into one cell

09:20 and what about the first employee peter

09:23 well

09:24 he didn't have this attribute he did not

09:26 have any specialization

09:27 and therefore it just shows no but no

09:29 errors

09:31 so now let's have a look at the last one

09:33 number three so

09:34 another hurdle that you could run into

09:36 when you are trying to pivot your data

09:38 set

09:39 and that is when you have no values

09:42 in the attribute column so here i made a

09:45 small change to the data set

09:46 you see inside of the attribute column i

09:49 now have

09:50 a null here and you might think okay

09:52 that's obvious

09:53 okay there's a no and might break the

09:55 whole thing however

09:56 it's not always that obvious if you have

09:58 a large data set

10:00 or maybe it's not even there in the

10:02 preview

10:03 then you might not even notice and it

10:05 doesn't work

10:06 let me show you which area you would get

10:08 so i'm going to

10:10 continue here and click on pivot and see

10:13 we cannot convert the value no

10:15 to type text so make sure that before

10:18 you pivot

10:18 you go one step before and choose the

10:21 attribute golem

10:22 and remove all of the empty cells in

10:24 there

10:28 and you see that would fix it i hope

10:31 that pivoting datasets is not going to

10:32 give you

10:33 any troubles anymore now maybe you run

10:36 into a scenario that wasn't covered

10:38 then let me know in the comment section

10:40 below and

10:42 if you haven't subscribed yet then make

10:43 sure to hit that subscribe button

10:45 i hope to see you in the next video

💫 FAQs about This YouTube Video

1. What is the process for pivoting a table in Power BI?

To pivot a table in Power BI, you need to select the column you want to pivot, go to the Transform tab, choose the Pivot Column option, and then select the value column. Ensure that you do not aggregate the values, especially if you want to show the actual values without any aggregation.

2. How can the 'too many elements in the enumeration' error be fixed when pivoting a table in Power BI?

The 'too many elements in the enumeration' error when pivoting a table in Power BI can be fixed by ensuring that there is a separate column for the attribute names to map the different attributes to an entity. This can be achieved by adding a conditional column for the entity names and filling any null values.

3. What are the two approaches to solve the pivot issue in Power BI?

There are two approaches to solve the pivot issue in Power BI. The first approach involves adding a third column for the entity names and the attributes to map the attributes to the entities. The second approach uses an index number for the entity within each table to map the attributes to the entities.

4. How can the 'too many elements in the enumeration' error be fixed when there are duplicates in the attribute column for the same entity in Power BI?

To fix the 'too many elements in the enumeration' error when there are duplicates in the attribute column for the same entity in Power BI, the data can be grouped by the entity and attribute, and the values can be combined using a suitable operation such as sum or text combine.

5. What potential issue should be considered when pivoting data in Power BI?

One potential issue to consider when pivoting data in Power BI is having empty or null values in the attribute column, which can cause errors during the pivot operation. Ensuring the data is clean and does not contain any empty or null values in the attribute column can help avoid this issue.

🎥 Related Videos