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

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

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?