Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jie09
Frequent Visitor

Error: column wasn't found

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!

7 REPLIES 7
lbendlin
Super User
Super User

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

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?

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.

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"
 
 

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

 

Can you post a couple rows from each source table?

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?

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors