Pivot column transform - occasional column

[Anonymous]

Morning all 

As context I have used the group by and pivot transformations to summarise which tracks responses to a survey. My issue arises when a particular column is not constant, for example the column is called 'request ruling' once this request has been actioned by staff the value becomes something else in the dataset. 

This vanishing column gives me a refresh error in the service, stating that column doesnt exist.

my question is there a way using grouo by and transform that i get get these request ruling column to stay. 

thanks 

James 

[artemus · Accepted solution]

For the pivot step, replace List.Distinct(...) with {"Value1", "Value2", "Value3", ...}

Replacing each "ValueN" with your values.

Alternativly, you can manually insert any that you are missing by replacing List.Distinct(...) with List.Distinct(... & {"MissingValue1", "MissingValue2", ...})

[Anonymous]

Legend thank you