Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
VladSorokin
New Member

Create a table based on another table using formula

Hi,

I'm very new to PowerQuery but have a task that makes me mad.

Imagine I have a table that possesses 3 columns: "Start Date", "End Date", and "Name". Say, this table contains information about sprints.

I need to convert this table into another one that contains only 2 columns: "Date" and "Name". So that each date within a period [StartDate...EndDate] gets a line within my second table.

 

Example:

Input Table:

Start DateEnd DateName
1 Jan 20235 Jan 2023Sprint 1
10 Jan 202311 Jan 2023Sprint 2

 

Output Table:

DateName
1 Jan 2023Sprint 1
2 Jan 2023Sprint 1
3 Jan 2023Sprint 1
4 Jan 2023Sprint 1
5 Jan 2023Sprint 1
10 Jan 2023Sprint 2
11 Jan 2023Sprint 2

 

It's clear to me that I should walk through the InputTable row by row and then fulfill the OutputTable using the row data. But... no idea how to do it in practice. Could someone help me with this?

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @VladSorokin ,

 

Please follow the steps below:

in power quert editor --> add a custom column:

List.Transform({Number.From([Start Date])..Number.From([End Date])},each Date.From(_))

vcgaomsft_0-1675235204846.png

expand [Date] and remove [Start Date] and [End Date]:

vcgaomsft_1-1675235300871.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

View solution in original post

3 REPLIES 3
v-cgao-msft
Community Support
Community Support

Hi @VladSorokin ,

 

Please follow the steps below:

in power quert editor --> add a custom column:

List.Transform({Number.From([Start Date])..Number.From([End Date])},each Date.From(_))

vcgaomsft_0-1675235204846.png

expand [Date] and remove [Start Date] and [End Date]:

vcgaomsft_1-1675235300871.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Thanks a lot, Gao, this works!!!

HotChilli
Super User
Super User

I won't give you the full answer but you can represent dates as numbers and then if you create a list using

{[start]..[end]} you can expand that 'to rows' and convert back to dates.

There are examples on the forum or on the web.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors