How to Transform table with Start Date and End Date with Status for specific date status

[Eli_]

I would like to convert my table
From 3 columns(Start Date, End Date and Stautus)
To 2 columns (Date(all dates included in start and end), Status)

so I can know what the status is for specific, days, month or year.

thsi is what the table currently looks like.

I just cant think of the best way posible to handle this..Every advise would be helpful

Idealy, I would imagine that just having a single date column and status would be the way to go, but not sure how to.

[v-juanli-msft]

Hi @Eli_ 

Select "start date" and "end date" columns, Unpivot columns.

After that, you can Add column->Date, add year, month, day columns.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jimmy801 · Accepted solution]

Hello @Eli_ 

check out this solution. It creates in a custom column a list with all dates. However, don't know how the first row should be treated.

let
	Source = #table
	(
		{"dStart","dEnd","STATUS"},
		{
			{"43844","43848","Occupied"},	{"43753","43766","Vacant"},	{"43831","43832","Occupied"}
		}
	),
	ToDate = Table.TransformColumns
	(
		Source,
		{
			{
				"dStart",
				each Date.From(Number.From(_)),
				type date
			},
			{
				"dEnd",
				each Date.From(Number.From(_)),
				type date
			}
		}
	),
	AddDates = Table.AddColumn
	(
		ToDate,
		"Dates",
		each List.Dates(_[dStart], Duration.TotalDays(_[dEnd]- _[dStart])+1,#duration(1,0,0,0))
	),
    Expand = Table.ExpandListColumn(AddDates, "Dates"),
    ChangeType = Table.TransformColumnTypes(Expand,{{"Dates", type date}})
in
	ChangeType

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy