Error: column wasn't found

Question

Hi everyone,&nbsp;I'm encountering an issue while refreshing my dataflow, specifically during the "WriteToDataDestination" activity. In my query, I combined several tables using the "Append Queries as New" function. After that, I performed various transformation actions such as adding columns, removing columns, pivoting, etc.&nbsp;The resulting view has several columns. The first column is a completely new column named "line" which contains two new text values. The remaining columns have new names such as "Jan-2024", "Feb-2024", "Jan-2025", etc., and their values are derived from the combined tables.&nbsp;I was able to successfully publish the dataflow, but I encountered the following error when attempting to refresh it:&nbsp;Append_WriteToDataDestination: Mashup Exception Expression Error Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: The column 'Jan-2024' of the table wasn't found. Details: Reason = Expression.Error;Detail = Jan-2024;Microsoft.Data.Mashup.Error.Context = User&nbsp;Any help or insights to resolve this issue would be greatly appreciated. Thank you!

lbendlin · Answer

Don't use values as column names, Unpivot your data to have a column for "time period" .

jie09 · Answer

Hi lbendlin, thank you for your help! Could you please elaborate on why I should restructure my data to have time periods as values in a single column instead of making them columns themselves? Currently, I am using a pivot operation to create columns like "Jan-2024", "Feb-2024", "Jan-2025", etc., because this format aligns with what my colleagues use for their analysis.&nbsp;Are you suggesting that having time periods as column names could be causing this error?

lbendlin · Answer

Yes, that is what I am suggesting.&nbsp; It is a typical pattern in Excel, but Power BI is not Excel. Power BI wants fixed column names and narrow tables.&nbsp; Let the visuals do the pivoting for you.

jie09 · Answer

Hi lbendlin,So now I'm not using pivot action, but I'm still getting the "Column wasn't found" error even though I can see them in the preview.Append_WriteToDataDestination: Mashup Exception Expression Error Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: The column 'Month-Year' of the table wasn't found. Details: Reason = Expression.Error; Detail = Month-Year; Microsoft.Data.Mashup.Error.Context = User&nbsp;Here I'm attaching my Power Query, thank you very much!let&nbsp; &nbsp; // Combine data from different years&nbsp; &nbsp; Source = Table.Combine({#"2024", #"2025", #"2026"}),&nbsp; &nbsp; // Remove blank rows&nbsp; &nbsp; #"Removed blank rows" = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),&nbsp; &nbsp; // Remove alternate rows&nbsp; &nbsp; #"Removed alternate rows" = Table.AlternateRows(#"Removed blank rows", 0, 4, 62),&nbsp; &nbsp; // Select relevant columns&nbsp; &nbsp; #"Choose columns" = Table.SelectColumns(#"Removed alternate rows", {"Column1", "Column7"}),&nbsp; &nbsp; // Fill down year values&nbsp; &nbsp; #"Filled down" = Table.FillDown(#"Choose columns", {"Column1"}),&nbsp; &nbsp; // Filter out rows that are not relevant (excluding unwanted row headers)&nbsp; &nbsp; #"Filtered rows" = Table.SelectRows(#"Filled down", each [Column1] &lt;&gt; "Average Rate" and [Column1] &lt;&gt; "Guestrooms" and [Column1] &lt;&gt; "Guestroom Revenue"),&nbsp; &nbsp; // Add a custom column to identify month abbreviations and combine with year&nbsp; &nbsp; #"Added Custom" = Table.AddColumn(#"Filtered rows", "Year", each if Value.Is([Column1], Int64.Type) then Number.ToText([Column1]) else null),&nbsp; &nbsp; #"Filled Down Year" = Table.FillDown(#"Added Custom", {"Year"}),&nbsp; &nbsp; // Replace null values in the Year column to prevent conversion errors&nbsp; &nbsp; #"Replaced Nulls in Year" = Table.ReplaceValue(#"Filled Down Year", null, "N/A", Replacer.ReplaceValue, {"Year"}),&nbsp; &nbsp; // Create a Month-Year column&nbsp; &nbsp; #"Month Year Column" = Table.AddColumn(#"Replaced Nulls in Year", "Month-Year", each&nbsp; &nbsp; &nbsp; &nbsp; let&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CurrentValue = [Column1],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Define month names and corresponding abbreviations&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MonthNames = {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"},&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MonthAbbreviations = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"},&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MonthIndex = List.PositionOf(MonthNames, CurrentValue),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IsMonth = MonthIndex &gt;= 0,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MonthAbbreviation = if IsMonth then MonthAbbreviations{MonthIndex} else null&nbsp; &nbsp; &nbsp; &nbsp; in&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if IsMonth then Text.Combine({MonthAbbreviation, "-", [Year]}) else null&nbsp; &nbsp; ),&nbsp; &nbsp; // Ensure there are no null values in Month-Year column&nbsp; &nbsp; #"Replaced Nulls in Month-Year" = Table.ReplaceValue(#"Month Year Column", null, "Invalid-Month", Replacer.ReplaceValue, {"Month-Year"}),&nbsp; &nbsp; // Remove original year rows if needed&nbsp; &nbsp; #"Removed Year Rows" = Table.SelectRows(#"Replaced Nulls in Month-Year", each not Value.Is([Column1], Int64.Type)),&nbsp; &nbsp; // Select and rename columns&nbsp; &nbsp; #"Choose columns 1" = Table.SelectColumns(#"Removed Year Rows", {"Column7", "Month-Year"}),&nbsp; &nbsp; #"Renamed columns" = Table.RenameColumns(#"Choose columns 1", {{"Column7", "Value"}}),&nbsp; &nbsp; // Add Line column with alternating values "Group_RN" and "Group_Rev"&nbsp; &nbsp; #"Add Index" = Table.AddIndexColumn(#"Renamed columns", "Index", 0, 1, Int64.Type),&nbsp; &nbsp; #"Add Line" = Table.AddColumn(#"Add Index", "Line", each if Number.Mod([Index], 2) = 0 then "Group_RN" else "Group_Rev"),&nbsp; &nbsp; #"Removed Columns" = Table.RemoveColumns(#"Add Line", {"Index"}),&nbsp; &nbsp; // Replace null values in Value column to prevent conversion errors&nbsp; &nbsp; #"Replaced Nulls in Value" = Table.ReplaceValue(#"Removed Columns", null, 0, Replacer.ReplaceValue, {"Value"}),&nbsp; &nbsp; #"Changed column type" = Table.TransformColumnTypes(#"Replaced Nulls in Value", {{"Value", Int64.Type}, {"Month-Year", type text}, {"Line", type text}})in&nbsp; &nbsp; #"Changed column type"&nbsp;&nbsp;

lbendlin · Answer

You will want to unpivot your source tables BEFORE you append them.
&nbsp;
Can you post a couple rows from each source table?

Forum Discussion

Error: column wasn't found

7 Replies

Related content

Reorder Column Error

Data source error: Expression. Error: The column wasn't found

error message

Multiple columns error?

error date column