从星期数，星期几和年份中获取日期PowerQuery M [英] Get date from weeknumber, dayofweek and year PowerQuery M

问题描述

我想为我的数据集创建一个额外的列.这需要通过在PowerQuery中使用M来完成.我有星期几，星期几和年份.

I want to create an extra column to my dataset. This needs to be done by using M in PowerQuery. I have the weeknumber, dayoftheweek and year available.

这可以通过在Excel中使用以下公式来完成.我需要类似的东西，但是在M中.

This can be done by using the following formula in Excel. I need something similar but then in M.

Date = DATE([year],1,-2)-WEEKDAY(DATE([year],1,3))+[week]*7 + [day]-1

例如:2019年的第2周，第3天，应为:9-1-2019(在#dd-mm-yyyy#中)

For example: Week 2, Day 3, Year 2019 should be: 9-1-2019 (In #dd-mm-yyyy#)

预先感谢

推荐答案

可以做到

= Table.AddColumn(Source, "Custom", each Date.From(Number.From(Date.AddDays(Date.FromText("1/1/"&Number.ToText([year])),-3))-Date.DayOfWeek(Date.FromText("1/3/"&Number.ToText([year])))-1+[week]*7+[day]-1) )

这篇关于从星期数，星期几和年份中获取日期PowerQuery M的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！