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.
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"
