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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Rune_
Frequent Visitor

Start date and end date

Hi 
I have three columns. 

Posting date, Job No_ and Job Task No_

 

I would like to make to extra columns with start date and end date

 

Start date would be List.Min of Posting date for specific Job No_ and Job Task No_

End date would be List.Max of Posting date for specific Job No_ and Job Task No_

 

Posting DateJob No_Job Task No_Start date
10.07.2018100780109.07.2018
13.07.2018100780109.07.2018
09.07.2018100780109.07.2018
09.07.2018100780109.07.2018
10.07.2018100780109.07.2018
10.07.2018100780109.07.2018
10.07.2018100780109.07.2018
10.07.2018100780109.07.2018
10.07.2018100780109.07.2018
10.07.2018100780109.07.2018

 

How would I write this in Power Query. 

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

Hello @Rune_ 

 

group your data on you job-columns and add 3 functions.

One all rows, one min on Posting date and one max on Posting date. Afterwards expand your AllRows-Column

Here a example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQMzDXMzIwtFDSAXIMzEG0gaFSrA5Qzhi3nIEleXJ47RsscrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Posting Date" = _t, #"Job No_" = _t, #"Job Task No_" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Posting Date", type date}, {"Job No_", Int64.Type}, {"Job Task No_", Int64.Type}},"de-DE"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Job No_", "Job Task No_"}, {{"AllRows", each _, type table [Posting Date=date, Job No_=number, Job Task No_=number]}, {"Start Date", each List.Min([Posting Date]), type date}, {"End Date", each List.Max([Posting Date]), type date}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Posting Date"}, {"Posting Date"})
in
    #"Expanded AllRows"

Jimmy801_0-1611659275825.png

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

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

View solution in original post

3 REPLIES 3
AlB
Super User
Super User

@Rune_ 

Another option, albeit perhaps slower:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQMzDXMzIwtFDSAXIMzEG0gaFSrA5Qzhi3nIEleXJ47RsscrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Posting Date" = _t, #"Job No_" = _t, #"Job Task No_" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Posting Date", type date}, {"Job No_", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Star Date", each List.Min(Table.SelectRows(#"Changed Type", (inner)=>[[Job No_], [Job Task No_]] = inner[[Job No_], [Job Task No_]])[Posting Date]), type date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "End Date", each List.Max(Table.SelectRows(#"Changed Type", (inner)=>[[Job No_], [Job Task No_]] = inner[[Job No_], [Job Task No_]])[Posting Date]), type date)
in
    #"Added Custom1"

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Jimmy801
Community Champion
Community Champion

Hello @Rune_ 

 

group your data on you job-columns and add 3 functions.

One all rows, one min on Posting date and one max on Posting date. Afterwards expand your AllRows-Column

Here a example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQMzDXMzIwtFDSAXIMzEG0gaFSrA5Qzhi3nIEleXJ47RsscrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Posting Date" = _t, #"Job No_" = _t, #"Job Task No_" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Posting Date", type date}, {"Job No_", Int64.Type}, {"Job Task No_", Int64.Type}},"de-DE"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Job No_", "Job Task No_"}, {{"AllRows", each _, type table [Posting Date=date, Job No_=number, Job Task No_=number]}, {"Start Date", each List.Min([Posting Date]), type date}, {"End Date", each List.Max([Posting Date]), type date}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Posting Date"}, {"Posting Date"})
in
    #"Expanded AllRows"

Jimmy801_0-1611659275825.png

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

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

AlB
Super User
Super User

Hi @Rune_ 

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data.

And please show the expected result for your sample data as well

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors