Forum Discussion

Eli_'s avatar
Eli_
Frequent Visitor
6 years ago
Solved

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

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.

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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

2 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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.