Generate a column for dates between start dates (list/ pivot?)

Hi WMart_AMS ,

Tha big question here is: why?
Why do you need the data in this format. Admittedly, your original data format is 'wrong' with the dated columns, but what's the bigger picture that requires the monthly dates between?
I only ask as there may be a faster/easier route to your end goal if I can understand the bigger picture.

Pete

WMart_AMS 
Please try with this query:

let
  Origen = Excel.CurrentWorkbook(){[ Name = "Tabla" ]}[Content], 
  #"Encabezados promovidos" = Table.PromoteHeaders ( Origen, [ PromoteAllScalars = true ] ), 
  #"Otras columnas con anulación de dinamización" = Table.UnpivotOtherColumns (
    #"Encabezados promovidos", 
    { "CountryID", "Channel" }, 
    "Atributo", 
    "Valor"
  ), 
  #"Dividir columna por delimitador" = Table.SplitColumn (
    #"Otras columnas con anulación de dinamización", 
    "Atributo", 
    Splitter.SplitTextByDelimiter ( " cost ", QuoteStyle.Csv ), 
    { "Atributo.1", "Atributo.2" }
  ), 
  #"Columna dinamizada" = Table.Pivot (
    #"Dividir columna por delimitador", 
    List.Distinct ( #"Dividir columna por delimitador"[Atributo.1] ), 
    "Atributo.1", 
    "Valor", 
    List.Sum
  )
in
  #"Columna dinamizada"

I hope this helps. If so please mark it as a solution. Kudos are welcome.

Hi WMart_AMS , Thank you for reaching out to the Microsoft Community Forum.

We find the answer shared by AlienSx  is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.

Thank you AlienSx  for your valuable response.

Hi WMart_AMS , Hope you are doing well. Kindly let us know if the issue has been resolved or if further assistance is needed. Your input could be helpful to others in the community.

Answer : 

M Query :
 

let

    Source = Excel.Workbook(File.Contents(Your file\Q2.xlsx"), null, true),

    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    // Standard headers step

    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CountryID", type text}, {"Channel", type text}}),

    // Unpivoting the cost columns because they are wide

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"CountryID", "Channel"}, "Cost Info", "Tarif"),

    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Cost Info", Splitter.SplitTextByEachDelimiter({" ("}, QuoteStyle.Csv, false), {"Cost Type", "Start Date"}),

    // Fix the date format - the brackets were annoying

    #"Added Custom" = Table.TransformColumns(#"Split Column by Delimiter", {{"Start Date", each Date.FromText(Text.BeforeDelimiter(_, ")")), type date}}),

    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"CountryID", Order.Ascending}, {"Channel", Order.Ascending}, {"Cost Type", Order.Ascending}, {"Start Date", Order.Ascending}}),

    // Doing the grouping to get the end dates

    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"CountryID", "Channel", "Cost Type"}, {{"Rows", each

        let

            Idx = Table.AddIndexColumn(_, "Index", 0, 1),

            NextDate = Table.AddColumn(Idx, "End Date", each try Idx[Start Date]{[Index] + 1} otherwise #date(2026, 12, 31), type date)

        in

            NextDate}}),

    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Start Date", "End Date", "Tarif"}),

    // Generate the months list

    #"Added Custom1" = Table.AddColumn(#"Expanded Rows", "Month", each List.Generate(() => [Start Date], (d) => d < [End Date], (d) => Date.AddMonths(d, 1))),

    #"Expanded Month" = Table.ExpandListColumn(#"Added Custom1", "Month"),

    #"Renamed Columns" = Table.RenameColumns(#"Expanded Month",{{"CountryID", "Country"}}),

    // Cleaning up columns at the end

    #"Removed Columns" = Table.SelectColumns(#"Renamed Columns",{"Country", "Channel", "Cost Type", "Month", "Tarif"}),

    #"Final Type Check" = Table.TransformColumnTypes(#"Removed Columns",{{"Tarif", type number}, {"Month", type date}})

in

    #"Final Type Check"