Error: column wasn't found

[Jie09]

Hi everyone,

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.

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.

I was able to successfully publish the dataflow, but I encountered the following error when attempting to refresh it:

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

Any help or insights to resolve this issue would be greatly appreciated. Thank you!

[lbendlin]

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

[Jie09]

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. Are you suggesting that having time periods as column names could be causing this error?

[lbendlin]

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

[Jie09]

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

Here I'm attaching my Power Query, thank you very much!

let

    // Combine data from different years

    Source = Table.Combine({#"2024", #"2025", #"2026"}),

    // Remove blank rows

    #"Removed blank rows" = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

    // Remove alternate rows

    #"Removed alternate rows" = Table.AlternateRows(#"Removed blank rows", 0, 4, 62),

    // Select relevant columns

    #"Choose columns" = Table.SelectColumns(#"Removed alternate rows", {"Column1", "Column7"}),

    // Fill down year values

    #"Filled down" = Table.FillDown(#"Choose columns", {"Column1"}),

    // Filter out rows that are not relevant (excluding unwanted row headers)

    #"Filtered rows" = Table.SelectRows(#"Filled down", each [Column1] <> "Average Rate" and [Column1] <> "Guestrooms" and [Column1] <> "Guestroom Revenue"),

    // Add a custom column to identify month abbreviations and combine with year

    #"Added Custom" = Table.AddColumn(#"Filtered rows", "Year", each if Value.Is([Column1], Int64.Type) then Number.ToText([Column1]) else null),

    #"Filled Down Year" = Table.FillDown(#"Added Custom", {"Year"}),

    // Replace null values in the Year column to prevent conversion errors

    #"Replaced Nulls in Year" = Table.ReplaceValue(#"Filled Down Year", null, "N/A", Replacer.ReplaceValue, {"Year"}),

    // Create a Month-Year column

    #"Month Year Column" = Table.AddColumn(#"Replaced Nulls in Year", "Month-Year", each

        let

            CurrentValue = [Column1],

            // Define month names and corresponding abbreviations

            MonthNames = {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"},

            MonthAbbreviations = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"},

            MonthIndex = List.PositionOf(MonthNames, CurrentValue),

            IsMonth = MonthIndex >= 0,

            MonthAbbreviation = if IsMonth then MonthAbbreviations{MonthIndex} else null

        in

            if IsMonth then Text.Combine({MonthAbbreviation, "-", [Year]}) else null

    ),

    // Ensure there are no null values in Month-Year column

    #"Replaced Nulls in Month-Year" = Table.ReplaceValue(#"Month Year Column", null, "Invalid-Month", Replacer.ReplaceValue, {"Month-Year"}),

    // Remove original year rows if needed

    #"Removed Year Rows" = Table.SelectRows(#"Replaced Nulls in Month-Year", each not Value.Is([Column1], Int64.Type)),

    // Select and rename columns

    #"Choose columns 1" = Table.SelectColumns(#"Removed Year Rows", {"Column7", "Month-Year"}),

    #"Renamed columns" = Table.RenameColumns(#"Choose columns 1", {{"Column7", "Value"}}),

    // Add Line column with alternating values "Group_RN" and "Group_Rev"

    #"Add Index" = Table.AddIndexColumn(#"Renamed columns", "Index", 0, 1, Int64.Type),

    #"Add Line" = Table.AddColumn(#"Add Index", "Line", each if Number.Mod([Index], 2) = 0 then "Group_RN" else "Group_Rev"),

    #"Removed Columns" = Table.RemoveColumns(#"Add Line", {"Index"}),

    // Replace null values in Value column to prevent conversion errors

    #"Replaced Nulls in Value" = Table.ReplaceValue(#"Removed Columns", null, 0, Replacer.ReplaceValue, {"Value"}),

    #"Changed column type" = Table.TransformColumnTypes(#"Replaced Nulls in Value", {{"Value", Int64.Type}, {"Month-Year", type text}, {"Line", type text}})

in

    #"Changed column type"

[lbendlin]

You will want to unpivot your source tables BEFORE you append them.

Can you post a couple rows from each source table?

[Jie09]

My source tables are somewhat messy, but they follow the exact same format. There are more than 10 columns in each source table, including time, budget, revenue, percentage, etc. Shall I clean/transform and unpivot all source tables and then append all together?

[lbendlin]

I cannot assist you if you are unable to provide sample data. I hope someone else can help you further.