Power Query - redundant data

%3CLINGO-SUB%20id%3D%22lingo-sub-2766430%22%20slang%3D%22en-US%22%3EPower%20Query%20-%20redundant%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2766430%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%20-%20dificult%20to%20describe%20the%20problem%20I%20am%20having%20in%20the%20subject%20line%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20relatively%20new%20to%20Power%20Query%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20creating%20custom%20columns%20to%20create%20data%20and%20then%20use%20the%20new%20data%20in%20pivot%20fields-%20however%20when%20I%20edit%20my%20code%20and%20say%20change%20one%20of%20the%20data%20entries%20-%20the%20old%20data%20entries%20still%20appear%20in%20my%20pivot%20tables%3C%2FP%3E%3CP%3Eie%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esay%20I%20have%20a%20custom%20column%20that%20looks%20for%20something%20in%20another%20column%20and%20uses%20an%20if%20statement%20and%20puts%20in%20the%20new%20column%20-%20so%20look%20for%20%22Apples%22%20in%20Column%20%22Basket%22%20and%20puts%20%22Fruit%22%20in%20the%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20if%20I%20go%20back%20and%20edit%20the%20code%20and%20replace%20%22Fruit%22%20in%20my%20if%20statement%20with%20say%20%22Fruits%22%20-%20both%20%22Fruit%22%20and%20%22Fruits%22%20are%20returned%20as%20options%20in%20my%20pivot%20table%20-%20even%20though%20there%20are%20no%20longer%20any%20%22Fruit%22%20in%20my%20column%20and%20the%20code%20has%20been%20changed%20to%20not%20use%20%22Fruit%22%20as%20an%20entry%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMore%20annoying%20-%20but%20as%20I%20am%20then%20using%20Slicers%20in%20my%20pivot%20tables%20the%20option%20of%20%22Fruit%22%20still%20shows%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2766430%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2766448%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20redundant%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2766448%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1160867%22%20target%3D%22_blank%22%3E%40Jesterc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20to%20fix%20your%20table%20in%20memory%20before%20pivoting.%20You%20may%20add%20Index%20column%20or%20use%20Table.Buffer().%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Sorry - dificult to describe the problem I am having in the subject line

 

Basically relatively new to Power Query

 

I am creating custom columns to create data and then use the new data in pivot fields- however when I edit my code and say change one of the data entries - the old data entries still appear in my pivot tables

ie

 

say I have a custom column that looks for something in another column and uses an if statement and puts in the new column - so look for "Apples" in Column "Basket" and puts "Fruit" in the column. 

 

However if I go back and edit the code and replace "Fruit" in my if statement with say "Fruits" - both "Fruit" and "Fruits" are returned as options in my pivot table - even though there are no longer any "Fruit" in my column and the code has been changed to not use "Fruit" as an entry

 

More annoying - but as I am then using Slicers in my pivot tables the option of "Fruit" still shows

 

 

2 Replies

@Jesterc 

Try to fix your table in memory before pivoting. You may add Index column or use Table.Buffer().

Ok - something for me to google