cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Reference Rows to return values when Condition is met

I want to run through rows of a table and return 2 date values based on the value of another column. In the example below, I would like to return a table with the  [ID], [Start] and [End] based on the value in [Condition]. I want to return the intial [ID] and [Start] and move through each row until [Condition]= "B" and return the [End]. I have read that loops in Power Query and DAX are known limitations but I'm wondering if someone has a good work around? I am having a tough time getting it right.

 

Source table:

ID
Start
End
Condition
1
1/1/2020
1/18/2020
A
1
1/18/2020
1/21/2020
A
1
1/21/2020
3/1/2020
A
1
3/1/2020
3/20/2020
B
1
3/20/2020
4/1/2020
B
2
1/10/2020
4/10/2020
B
3
2/11/2020
3/5/2020
A
3
3/5/2020
3/8/2020
B
3
3/8/2020
5/1/2020
B

 

Table to be returned:

ID
Start
End
1
1/1/2020
3/20/2020
1
3/20/2020
4/1/2020
2
1/10/2020
4/10/2020
3
2/11/2020
3/8/2020
3
3/8/2020
5/1/2020
 
 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II
Resolver II

Re: Reference Rows to return values when Condition is met

@ebottom

 

Was the last record in your simulation result table wrong? Shouldn't there be two records?

1.png

If what I understand is correct, you can try my code

 

 

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65W8nRRsjLUUQouSSwqUbJSMozRByIjAyMDJR0l17wUqJAFXMw5Py8lsyQzPw8o46hUq4PVBAtMI4wMSTLCCN0VxsgOI2iCMaY3jEF8rCY44TABSTnECBNcbkCYYIQaENhMIOQIY4QRRkDlWPxhSiAgjFG8YYppAPboxOoEFNUQA0zxBEMsAA==",BinaryEncoding.Base64),Compression.Deflate))),
    rows = Table.ToRows(Source)&{{null,null,null,null}},
    acc = List.Accumulate(
             rows,
             {"","","",{}},//comparer/Start_date status/End_date status/output
             (s,c)=>if s{0}="" then {c{3}, c, c, s{3}}
                    else if s{0}<>"B" then { c{3}, s{1}, c, s{3}}
                    else { c{3}, c, c, s{3}&{{s{1}{0}, s{1}{1}, s{2}{2}}}}
          ){3},
    tbl = Table.FromRows(acc,List.RemoveLastN(Table.ColumnNames(Source))),
    result = Table.TransformColumnTypes(tbl,{{"ID", Int64.Type}, {"Start", type date}, {"End", type date}})
in
    result

 

 

View solution in original post

3 REPLIES 3
Highlighted
Resolver II
Resolver II

Re: Reference Rows to return values when Condition is met

@ebottom

 

Was the last record in your simulation result table wrong? Shouldn't there be two records?

1.png

If what I understand is correct, you can try my code

 

 

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65W8nRRsjLUUQouSSwqUbJSMozRByIjAyMDJR0l17wUqJAFXMw5Py8lsyQzPw8o46hUq4PVBAtMI4wMSTLCCN0VxsgOI2iCMaY3jEF8rCY44TABSTnECBNcbkCYYIQaENhMIOQIY4QRRkDlWPxhSiAgjFG8YYppAPboxOoEFNUQA0zxBEMsAA==",BinaryEncoding.Base64),Compression.Deflate))),
    rows = Table.ToRows(Source)&{{null,null,null,null}},
    acc = List.Accumulate(
             rows,
             {"","","",{}},//comparer/Start_date status/End_date status/output
             (s,c)=>if s{0}="" then {c{3}, c, c, s{3}}
                    else if s{0}<>"B" then { c{3}, s{1}, c, s{3}}
                    else { c{3}, c, c, s{3}&{{s{1}{0}, s{1}{1}, s{2}{2}}}}
          ){3},
    tbl = Table.FromRows(acc,List.RemoveLastN(Table.ColumnNames(Source))),
    result = Table.TransformColumnTypes(tbl,{{"ID", Int64.Type}, {"Start", type date}, {"End", type date}})
in
    result

 

 

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Reference Rows to return values when Condition is met

Hi @ebottom 

 

If you prefer a no-code solution please see the below or see the attached.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY2xDcAgDAR3cU1hv4NEm6yB2H+NIDtgktCd7jBfKwklAoNZOFBKx5NaWrtJR8imY6Lypmv8r+CO16ebNDzsqXfEfnSJex0Sskzlua9v+WD537s0zGO/3Q==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Start = _t, End = _t, Condition = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}, {"ID", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"End", Order.Descending}}),
    #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Custom", each if [Condition] = "B" then [End] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"End"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "End"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"End", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"ID", "End"}, {{"Start", each List.Min([Start]), type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"ID", "Start", "End"})
in
    #"Reordered Columns"

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Highlighted
Frequent Visitor

Re: Reference Rows to return values when Condition is met

Yes- apologize for the error on ID=3. This has been corrected. Thank you for your help! 

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021