Solved: Generate a column for dates between start dates (l...

WMart_AMS · ‎02-16-2026

Hi all,

I am looking for a solution in power query for the following.

I have an overview with shipping and return costs per year, country and channel. the costs change every now and then.

my table looks like this.

CountryID	Channel	Shipping cost 01-01-2024	Return cost 01-01-2024	Shipping cost 01-01-2025	Return cost 01-01-2025	Shipping cost 01-07-2025	Return cost 01-07-2025	Shipping cost 01-01-2026	Return cost 01-01-2026
NL	Marktplaats	€ 4,64	€ 4,64	€ 4,63	€ 4,63	€ 5,38	€ 5,38	€ 4,63	€ 4,63
NL	webshop	€ 4,64	€ 2,13	€ 4,63	€ 2,13	€ 5,38	€ 2,88	€ 4,63	€ 2,13
DE	Marktplaats	€ 5,75	€ 8,23	€ 4,76	€ 6,25	€ 5,51	€ 7,00	€ 4,76	€ 6,25
DE	webshop	€ 5,75	€ 5,73	€ 4,76	€ 3,75	€ 5,51	€ 4,50	€ 4,76	€ 3,75
BE	Marktplaats	€ 6,11	€ 8,58	€ 4,75	€ 5,53	€ 5,50	€ 6,28	€ 4,75	€ 5,53
BE	webshop	€ 6,11	€ 6,08	€ 4,75	€ 3,03	€ 5,50	€ 3,78	€ 4,75	€ 3,03
FR	Marktplaats	€ 6,70	€ 9,67	€ 5,56	€ 7,40	€ 6,31	€ 8,15	€ 5,56	€ 7,40
FR	webshop	€ 6,70	€ 7,17	€ 5,56	€ 4,90	€ 6,31	€ 5,65	€ 5,56	€ 4,90
IT	Marktplaats	€ 8,82	€ 10,76	€ 6,70	€ 9,53	€ 7,45	€ 10,28	€ 6,70	€ 9,53
IT	webshop	€ 8,82	€ 8,26	€ 6,70	€ 7,03	€ 7,45	€ 7,78	€ 6,70	€ 7,03
AT	Marktplaats	€ 6,70	€ 9,67	€ 6,53	€ 8,19	€ 7,28	€ 8,94	€ 6,53	€ 8,19
AT	webshop	€ 6,70	€ 7,17	€ 6,53	€ 5,69	€ 7,28	€ 6,44	€ 6,53	€ 5,69
ES	Marktplaats	€ 8,67	€ 10,20	€ 8,38	€ 10,28	€ 9,13	€ 11,03	€ 8,38	€ 10,28
ES	webshop	€ 8,67	€ 7,70	€ 8,38	€ 7,78	€ 9,13	€ 8,53	€ 8,38	€ 7,78

I am looking for a query that generates a list of dates in between the startdate of each series of tarifs for shipping that distinguishes country and channel as wel. I want to have an column that shows these tarifs per month. so for example, the result should show:

- the shipping costs between 01-01-2024 and 01-01-2025, between 01-01-2025 and 01-07-2025 etc. for each country and channel

- the return shipping costs 01-01-2024 and 01-01-2025, between 01-01-2025 and 01-07-2025 etc. for each country and channel

The result i'm looking for should be something like this:

Country	Channel	Cost type	Month	Tarif
NL	Markplaats	Shipping	1-1-2025	€ 4,63
NL	Markplaats	Shipping	1-2-2025	€ 4,63
NL	Markplaats	Shipping	1-3-2025	€ 4,63
NL	Markplaats	Shipping	1-4-2025	€ 4,63
NL	Markplaats	Shipping	1-5-2025	€ 4,63
NL	Markplaats	Shipping	1-6-2025	€ 4,63
NL	Markplaats	Shipping	1-7-2025	€ 5,38
NL	Markplaats	Shipping	1-8-2025	€ 5,38
NL	Markplaats	Shipping	1-9-2025	€ 5,38
NL	Markplaats	Shipping	1-10-2025	€ 5,38
NL	Markplaats	Shipping	1-11-2025	€ 5,38
NL	Markplaats	Shipping	1-12-2025	€ 5,38
NL	Markplaats	Shipping	1-1-2026	€ 4,63
NL	Markplaats	Shipping	1-2-2026	€ 4,63
NL	Markplaats	Shipping	1-3-2026	€ 4,63
NL	Markplaats	Return	1-1-2025	€ 4,63
NL	Markplaats	Return	1-2-2025	€ 4,63
NL	Markplaats	Return	1-3-2025	€ 4,63
NL	Markplaats	Return	1-4-2025	€ 4,63
NL	Markplaats	Return	1-5-2025	€ 4,63
NL	Markplaats	Return	1-6-2025	€ 4,63
NL	Markplaats	Return	1-7-2025	€ 5,38
NL	Markplaats	Return	1-8-2025	€ 5,38
NL	Markplaats	Return	1-9-2025	€ 5,38
NL	Markplaats	Return	1-10-2025	€ 5,38
NL	Markplaats	Return	1-11-2025	€ 5,38
NL	Markplaats	Return	1-12-2025	€ 5,38
NL	Markplaats	Return	1-1-2026	€ 4,63
NL	Markplaats	Return	1-2-2026	€ 4,63
NL	Markplaats	Return	1-3-2026	€ 4,63
NL	webshop
NL	webshop
NL	webshop
NL	webshop

Can anyone provide me with the right solution?

ralf_anton · ‎02-16-2026

Hi,

ich hoffe, die Datenqualität ist im Original besser...;)

So wie ich das verstanden habe, könntest Du das folgendermaßen lösen:

Abfrage 1: Alle Versandkosten:

M-Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    Tarifspalten = Table.ColumnNames(Quelle),
    #"Entfernte oberste Elemente" = List.Skip(Tarifspalten,2),
    #"In Tabelle konvertiert" = Table.FromList(#"Entfernte oberste Elemente", Splitter.SplitTextByDelimiter(" "), null, null, ExtraValues.Error),
    #"Gefilterte Zeilen" = Table.SelectRows(#"In Tabelle konvertiert", each ([Column1] = "Rückgabekosten")),
    ColToDel =  Table.AddColumn(#"Gefilterte Zeilen", "Relevant", each [Column1] & " " & [Column2])[Relevant],
    
    #"Entfernte Spalten" = Table.RemoveColumns(Quelle,ColToDel),
    #"Entpivotierte Spalten" = Table.UnpivotOtherColumns(#"Entfernte Spalten", {"CountryID", "Kanal"}, "Attribut", "Wert"),
    #"Spalte nach Trennzeichen teilen" = Table.SplitColumn(#"Entpivotierte Spalten", "Attribut", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Tarif", "Datum"})
in
    #"Spalte nach Trennzeichen teilen"

Abfrage 2: Alle Rückgaben, an die die Versanddaten angehängt werden. (Natürlich kann man das auch in einer Abfrage erledigen, aber so wird es hoffentlich klarer)

M-Code:

    let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    Tarifspalten = Table.ColumnNames(Quelle),
    #"Entfernte oberste Elemente" = List.Skip(Tarifspalten,2),
    #"In Tabelle konvertiert" = Table.FromList(#"Entfernte oberste Elemente", Splitter.SplitTextByDelimiter(" "), null, null, ExtraValues.Error),
    #"Gefilterte Zeilen" = Table.SelectRows(#"In Tabelle konvertiert", each ([Column1] = "Versandkosten")),
    ColToDel =  Table.AddColumn(#"Gefilterte Zeilen", "Relevant", each [Column1] & " " & [Column2])[Relevant],
    
    #"Entfernte Spalten" = Table.RemoveColumns(Quelle,ColToDel),
    #"Entpivotierte Spalten" = Table.UnpivotOtherColumns(#"Entfernte Spalten", {"CountryID", "Kanal"}, "Attribut", "Wert"),
    #"Spalte nach Trennzeichen teilen" = Table.SplitColumn(#"Entpivotierte Spalten", "Attribut", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Tarif", "Datum"}),
    #"Angefügte Abfrage" = Table.Combine({#"Spalte nach Trennzeichen teilen", TarifVersand})
in
    #"Angefügte Abfrage"

Hier kannst Du die Beispieldatei herunterladen.

View solution in original post

AlienSx · ‎02-16-2026

let
    fx_dates = (s, e) => List.Generate(() => s, (x) => x < e, (x) => Date.AddMonths(x, 1)),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    eff_dates = List.Buffer(
        List.Sort(
            List.Distinct(
                List.Transform(
                    List.Skip(Table.ColumnNames(Source), 2), 
                    (x) => Date.From(Text.AfterDelimiter(x, "cost "), "de")
                )
            )
        ) & {Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()), 2))}
    ),
    all_dates = List.Buffer(
        List.Transform(
            List.RemoveLastN(List.Positions(eff_dates)),
            (x) => fx_dates(eff_dates{x}, eff_dates{x + 1})
        )
    ),
    upvt = Table.UnpivotOtherColumns(Source, {"CountryID", "Channel"}, "Attribute", "Tarif"),
    split = Table.SplitColumn(upvt, "Attribute", Splitter.SplitTextByDelimiter(" cost "), {"Cost type", "Date"}),
    tx = Table.TransformColumns(
        split, 
        {
            {"Date", (x) => all_dates{List.PositionOf(eff_dates, Date.From(x, "de"))}},
            {"Tarif", (x) => Value.FromText(x, "de")}
        }
    ),
    z = Table.ExpandListColumn(tx, "Date")
in
    z

View solution in original post

Ray_Minds

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"

v-hashadapu · ‎02-22-2026

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.

v-hashadapu · ‎02-19-2026

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.

pcoley · ‎02-17-2026

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

AlienSx · ‎02-16-2026

let
    fx_dates = (s, e) => List.Generate(() => s, (x) => x < e, (x) => Date.AddMonths(x, 1)),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    eff_dates = List.Buffer(
        List.Sort(
            List.Distinct(
                List.Transform(
                    List.Skip(Table.ColumnNames(Source), 2), 
                    (x) => Date.From(Text.AfterDelimiter(x, "cost "), "de")
                )
            )
        ) & {Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()), 2))}
    ),
    all_dates = List.Buffer(
        List.Transform(
            List.RemoveLastN(List.Positions(eff_dates)),
            (x) => fx_dates(eff_dates{x}, eff_dates{x + 1})
        )
    ),
    upvt = Table.UnpivotOtherColumns(Source, {"CountryID", "Channel"}, "Attribute", "Tarif"),
    split = Table.SplitColumn(upvt, "Attribute", Splitter.SplitTextByDelimiter(" cost "), {"Cost type", "Date"}),
    tx = Table.TransformColumns(
        split, 
        {
            {"Date", (x) => all_dates{List.PositionOf(eff_dates, Date.From(x, "de"))}},
            {"Tarif", (x) => Value.FromText(x, "de")}
        }
    ),
    z = Table.ExpandListColumn(tx, "Date")
in
    z

ralf_anton · ‎02-16-2026

Hi,

ich hoffe, die Datenqualität ist im Original besser...;)

So wie ich das verstanden habe, könntest Du das folgendermaßen lösen:

Abfrage 1: Alle Versandkosten:

M-Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    Tarifspalten = Table.ColumnNames(Quelle),
    #"Entfernte oberste Elemente" = List.Skip(Tarifspalten,2),
    #"In Tabelle konvertiert" = Table.FromList(#"Entfernte oberste Elemente", Splitter.SplitTextByDelimiter(" "), null, null, ExtraValues.Error),
    #"Gefilterte Zeilen" = Table.SelectRows(#"In Tabelle konvertiert", each ([Column1] = "Rückgabekosten")),
    ColToDel =  Table.AddColumn(#"Gefilterte Zeilen", "Relevant", each [Column1] & " " & [Column2])[Relevant],
    
    #"Entfernte Spalten" = Table.RemoveColumns(Quelle,ColToDel),
    #"Entpivotierte Spalten" = Table.UnpivotOtherColumns(#"Entfernte Spalten", {"CountryID", "Kanal"}, "Attribut", "Wert"),
    #"Spalte nach Trennzeichen teilen" = Table.SplitColumn(#"Entpivotierte Spalten", "Attribut", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Tarif", "Datum"})
in
    #"Spalte nach Trennzeichen teilen"

Abfrage 2: Alle Rückgaben, an die die Versanddaten angehängt werden. (Natürlich kann man das auch in einer Abfrage erledigen, aber so wird es hoffentlich klarer)

M-Code:

    let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    Tarifspalten = Table.ColumnNames(Quelle),
    #"Entfernte oberste Elemente" = List.Skip(Tarifspalten,2),
    #"In Tabelle konvertiert" = Table.FromList(#"Entfernte oberste Elemente", Splitter.SplitTextByDelimiter(" "), null, null, ExtraValues.Error),
    #"Gefilterte Zeilen" = Table.SelectRows(#"In Tabelle konvertiert", each ([Column1] = "Versandkosten")),
    ColToDel =  Table.AddColumn(#"Gefilterte Zeilen", "Relevant", each [Column1] & " " & [Column2])[Relevant],
    
    #"Entfernte Spalten" = Table.RemoveColumns(Quelle,ColToDel),
    #"Entpivotierte Spalten" = Table.UnpivotOtherColumns(#"Entfernte Spalten", {"CountryID", "Kanal"}, "Attribut", "Wert"),
    #"Spalte nach Trennzeichen teilen" = Table.SplitColumn(#"Entpivotierte Spalten", "Attribut", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Tarif", "Datum"}),
    #"Angefügte Abfrage" = Table.Combine({#"Spalte nach Trennzeichen teilen", TarifVersand})
in
    #"Angefügte Abfrage"

Hier kannst Du die Beispieldatei herunterladen.

BA_Pete · ‎02-16-2026

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

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

Helpful resources

Join our Fabric User Panel

Power BI Monthly Update - February 2026

FabCon Atlanta 2026

FabCon is coming to Atlanta

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

Helpful resources

Join our Fabric User Panel

Power BI Monthly Update - February 2026

FabCon Atlanta 2026