Power Query text/date help!

[matt3388]

Hello,

I've been trying to calculate a date from a week commencing date and day of the week (Mon, Tue, etc), but I'm struggling with Mon, Tue, being stored as text values.

I have the week commencing date which is stored as a date format in column one, titled "week commencing", and the day of the week (Mon, Tue etc) stored in text format in column two, titled "Day completed".

I'm looking to add a custom column which will give me the correct completion date for Tue, Wed, Thu etc, in the format of dd/mm/yyyy.

I want the week to run from Mon-Sun, so the date for Monday would match the week commencing date in column one.

Any help or ideas would be really appreciated, thank you.




I'm also using excel 365 on Windows 11

 

[Matt Allington]

There are various ways this could be done. Maybe the most straight forward (in PQ) would be to load a new table of master data with the list of days and an integer.
Mon,0
Tue,1
Wed,2
Etc

Merge the original table with this new table and extract the integer column
Add a new custom column that adds the integer to the date
Make sure the original date column is date and not date/time, first
Custom column will need to be something like
[original date column name] + #duration([integer column name],0,0,0)

 

[smozgur]

Maybe something like this as an alternative? Just create a new blank query and paste the following code in the Advanced Editor dialog. Then you can adapt it to your own source.
I don't think you need the last step since you'll most likely need to format it in Excel, but here it is. ChangeType -> Using Local can be also used.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PS0msVDAwUghOLShJzU1KLVIwMjAyUTAwsAIjJR2oGqVYnSGoPKQ0tXgwqQ9PTcmjo45YAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Week commencing" = _t, #"Day completed" = _t]),
    ChangeTypes = Table.TransformColumnTypes(Source,{{"Week commencing", type datetime}, {"Day completed", type text}}),
    AddDayIndex = Table.AddColumn(ChangeTypes, "DayIndex", each List.PositionOf({"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"}, [Day completed])),
    ActualDayCompleted = Table.AddColumn(AddDayIndex, "Actual Day completed", each Date.AddDays([Week commencing], [DayIndex])),
    UnnecessaryFormatStep = Table.TransformColumns(ActualDayCompleted, {"Actual Day completed", each DateTime.ToText(_, [Format = "dd/MM/yyyy"])})
in
    UnnecessaryFormatStep

Week commencing	Day completed	DayIndex	Actual Day completed
2.09.2024 00:00	Monday	0	02.09.2024
2.09.2024 00:00	Monday	0	02.09.2024
2.09.2024 00:00	Monday	0	02.09.2024
2.09.2024 00:00	Monday	0	02.09.2024
2.09.2024 00:00	Tuesday	1	03.09.2024
2.09.2024 00:00	Tuesday	1	03.09.2024
2.09.2024 00:00	Tuesday	1	03.09.2024
2.09.2024 00:00	Wednesday	2	04.09.2024
2.09.2024 00:00	Wednesday	2	04.09.2024
2.09.2024 00:00	Wednesday	2	04.09.2024