BUG - Power Query breaks after Table.Pivot

[Syndicate_Admin]

My dataflow works fine until a Table.Pivot step.

After that step, I get an error for each and every transformation I do on columns.

Basically, the error message is:

There was a problem refreshing the dataflow, the dataflow definition contained errors. Please fix the problem and try again. Additional information: Unexpected exception, Message: <pi>Expression.Error: The column 'ColumnName' of the table wasn't found.</pi>. (Request ID: 2
42f48f0ae3).

[Syndicate_Admin]

Any additional transformation will result in the error above, when refreshing the Dataflow.

Example:

Table.TransformColumns(Custom, {{"NomeCompleto", each Text.Lower(_), type nullable text}})

[wdx223_Daniel]

there is no column named as NomeCompleto in your table?

to check, if there is the value of "NomeCompleto" in the column of "question_text"

[Syndicate_Admin]

Hello,

Here it is:

let
    Source = Results_Joomla,
    #"Filtered rows" = Table.SelectRows(Source, each ([quiz_id] = 8 or [quiz_id] = 22)),
    #"Choose columns" = Table.SelectColumns(#"Filtered rows", {"user_id", "datetime_taken", "Id_Evento", "quiz_name", "question_text", "titulo_evento", "answer_texto", "OnlinePresencial"}),
    #"Capitalized each word" = Table.TransformColumns(#"Choose columns", {{"question_text", each Text.Proper(_), type nullable text}}),
    #"Trimmed text" = Table.TransformColumns(#"Capitalized each word", {{"question_text", each Text.Trim(_), type nullable text}}),
    #"Replaced value" = Table.ReplaceValue(#"Trimmed text", " ", "", Replacer.ReplaceText, {"question_text"}),
    #"Replaced value 1" = Table.ReplaceValue(#"Replaced value", "(", "", Replacer.ReplaceText, {"question_text"}),
    #"Replaced value 3" = Table.ReplaceValue(#"Replaced value 1", ")", "", Replacer.ReplaceText, {"question_text"}),
    #"Replaced value 4" = Table.ReplaceValue(#"Replaced value 3", "/", "", Replacer.ReplaceText, {"question_text"}),
    #"Replaced value 5" = Table.ReplaceValue(#"Replaced value 4", "º", "", Replacer.ReplaceText, {"question_text"}),
    #"Replaced value 6" = Table.ReplaceValue(#"Replaced value 5", ".", "", Replacer.ReplaceText, {"question_text"}),
    #"Replaced value 7" = Table.ReplaceValue(#"Replaced value 6", ",", "", Replacer.ReplaceText, {"question_text"}),
    #"Replaced value 8" = Table.ReplaceValue(#"Replaced value 7", "-", "", Replacer.ReplaceText, {"question_text"}),
    #"Extracted first characters" = Table.TransformColumns(#"Replaced value 8", {{"question_text", each Text.Start(_, 30), type text}}),
    #"Colunas Reordenadas" = Table.ReorderColumns(#"Extracted first characters", {"user_id", "Id_Evento", "quiz_name", "answer_texto", "question_text"}),
    #"Trimmed text 1" = Table.TransformColumns(#"Colunas Reordenadas", {{"answer_texto", each Text.Trim(_), type nullable text}}),
    Custom = Table.Pivot(#"Trimmed text 1", List.Distinct(#"Trimmed text 1"[question_text]), "question_text", "answer_texto")
in
    Custom

This works perfectly.

Adding a new step after "Custom", like formatting a column to lowercase or some other transformation, will break the dataflow with the error above.

[wdx223_Daniel]

let
    Source = Results_Joomla,
    #"Filtered rows" = Table.SelectRows(Source, each ([quiz_id] = 8 or [quiz_id] = 22)),
    #"Choose columns" = Table.SelectColumns(#"Filtered rows", {"user_id", "datetime_taken", "Id_Evento", "quiz_name", "question_text", "titulo_evento", "answer_texto", "OnlinePresencial"}),
    CleanData=Table.TransformColumns(#"Choose columns",{{"question_text",each Text.Start(Text.Combine(Text.SplitAny(Text.Trim(Text.Proper(_))," ()/º.,-")),30)},{"answer_texto", each Text.Trim(_), type nullable text}}),
    #"Colunas Reordenadas" = Table.ReorderColumns(CleanData, {"user_id", "Id_Evento", "quiz_name", "answer_texto", "question_text"}),
    Custom = Table.Pivot(#"Colunas Reordenadas", List.Distinct(#"Colunas Reordenadas"[question_text]), "question_text", "answer_texto")
in
    Custom

your code looks good, but you still can still try my simplified code.

what code did you add after the Custom step?

[wdx223_Daniel]

please show us your code