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

[WMart_AMS]

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 · Accepted solution]

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.

[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]

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]

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]

@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 · Accepted solution]

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 · Accepted solution]

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]

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