Forum Discussion

Jie09's avatar
Jie09
Frequent Visitor
2 years ago

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...
Need Help
Jie09's avatar
Jie09
Frequent Visitor
2 years ago

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's avatar
lbendlin
Super User
2 years ago

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's avatar
    Jie09
    Frequent Visitor
    2 years ago

    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's avatar
      lbendlin
      Super User
      2 years ago

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

       

      Can you post a couple rows from each source table?

      • Jie09's avatar
        Jie09
        Frequent Visitor
        2 years ago

        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?