PQ -Date Distribution

[AllanBerces]

Hi good day, can anyone help me on my PQ. I have a table and the date column is showing only start and end of the week, how can i distribute it per day.

DESIRED OUTPUT

Thank you

[v-tejrama]

Hi @AllanBerces ,

Has your issue been resolved?If the response provided by @danextian addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[danextian · Accepted solution]

hi @AllanBerces 

Try the following custom column

let
    // Split date range string into list, e.g. "13/12 - 05/01" -> {"13/12", "05/01}
    split = Text.Split([Date], " - "),

    // Append year to each date part
    addYear = List.Transform(split, each _ & "/2025"),

    // Convert each string to a date using "en-gb" format
    toDate = List.Transform(addYear, each Date.From(_, "en-gb")),

    // Ensure start date is not after end date (handle year rollover)
    startDate = Number.From(
        if toDate{0} > toDate{1}
        then Date.AddYears(toDate{0}, -1)
        else toDate{0}
    ),

    // Convert end date to number
    endDate = Number.From(toDate{1}),

    // Generate list of date numbers between start and end
    dateAsNumber = {startDate..endDate},

    // Convert number list back to dates
    dateAsNumberToDate = List.Transform(dateAsNumber, Date.From)
in
    dateAsNumberToDate

This  will create a column containt lists of dates which can be expanded into individual date rows

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTWNzRS0FUwMNU3MFSK1YlWMjADsoAihkZgkVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each let
    // Split date range string into list, e.g. "13/12 - 05/01" -> {"13/12", "05/01}
    split = Text.Split([Date], " - "),

    // Append year to each date part
    addYear = List.Transform(split, each _ & "/2025"),

    // Convert each string to a date using "en-gb" format
    toDate = List.Transform(addYear, each Date.From(_, "en-gb")),

    // Ensure start date is not after end date (handle year rollover)
    startDate = Number.From(
        if toDate{0} > toDate{1}
        then Date.AddYears(toDate{0}, -1)
        else toDate{0}
    ),

    // Convert end date to number
    endDate = Number.From(toDate{1}),

    // Generate list of date numbers between start and end
    dateAsNumber = {startDate..endDate},

    // Convert number list back to dates
    dateAsNumberToDate = List.Transform(dateAsNumber, Date.From)
in
    dateAsNumberToDate),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates")
in
    #"Expanded Dates"

[AllanBerces]

Hi @danextian thank you very much, big help and save me a lot of time.