chose between sets of records based on status

[mgrayTCB]

I am not sure where to begin with this in power queary. I have bunch of tables that have data by project number that is either "final", "current", or both "final" and "current" records. For projects that have both final and current I alway only want final, otherwise if there is only one set for a particular project I will use the one that exists. In the example below I removed the project 1 "current" records becuase there are final records. For project 3 I use current becuase that is all that exits.

Any help would be appreciated.

Simplified data would look like this:

Input Table
ProjID	Status	Year	Amt
1	final	2018	77
1	final	2019	55
1	final	2020	65
1	final	2021	35
1	current	2019	50
1	current	2020	60
1	current	2021	70
1	current	2018	66
2	final	2019	100
2	final	2020	500
2	final	2021	300
3	current	2019	666
3	current	2020	777
3	current	2021	666
Output Table
ProjID	Status	Year	Amt
1	final	2018	77
1	final	2019	55
1	final	2020	65
1	final	2021	35
2	final	2019	100
2	final	2020	500
2	final	2021	300
3	current	2019	666
3	current	2020	777
3	current	2021	666

[AlB · Accepted solution]

Hi @mgrayTCB 

Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBNCoAgEAbQu7h2MaOodRZpEVEQhAup+zdORpHT6kMfzo8xKlRaLWsaN0oD2FGEoAbdSE/hnCAGKLwo5WgfmY6c57S/6oFoV8UfKxdBNp7eezbTTo8AAnEzJxPPX8lKC/ja7YtcNNSPbBDvl8MJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjID = _t, Status = _t, Year = _t, Amt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjID", Int64.Type}, {"Status", type text}, {"Year", Int64.Type}, {"Amt", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ProjID"}, {{"Result", each List.Max(List.Distinct([Status])), type nullable text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ProjID"}, #"Grouped Rows", {"ProjID"}, "merge_", JoinKind.LeftOuter),
    #"Expanded merge_" = Table.ExpandTableColumn(#"Merged Queries", "merge_", {"Result"}, {"Result"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded merge_", each ([Result] = [Status])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Result"})
in
    #"Removed Columns"

Please accept the solution 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.

[AlB · Accepted solution]

@mgrayTCB 

And if there's nor final nor current?

Pending that clarification, here's an alternative. Note the only change is around that List.Max()  in the #"Grouped Rows" step

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddFNDoMgEAXgu7B2MaD8ncW4aIxNTYwmRO8vTFGa8lxN4Asz8Oh7IUUj3vP6WmJVJF0s1oqhqcTHojUQRbEYKGnZFhmPEKZ1/+lH0L4dHyxtWGx8e2Nu2/bPFMo074HwLOeQpGXXoW6uZKHqlCQRIB6kMXFOmVoUlMmv+kduavOHVSivk8MJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjID = _t, Status = _t, Year = _t, Amt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjID", Int64.Type}, {"Status", type text}, {"Year", Int64.Type}, {"Amt", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ProjID"}, {{"Result", each if List.Contains([Status], "final") then "final" else if List.Contains([Status], "current") then "current" else null , type nullable text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ProjID"}, #"Grouped Rows", {"ProjID"}, "merge_", JoinKind.LeftOuter),
    #"Expanded merge_" = Table.ExpandTableColumn(#"Merged Queries", "merge_", {"Result"}, {"Result"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded merge_", each ([Result] = [Status])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Result"})
in
    #"Removed Columns"

Please accept the solution 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.

[mgrayTCB]

If there is no final or current I would want nothing. I guess by filtering out "other" first your orignal code would work but was looking for something more precise that max of sorted text. Something that could be adapted to explicity pull certain status tags if there are more than one.

[AlB]

@mgrayTCB 

I've edited my previous response. See if that does the trick

Please accept the solution 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.

[AlB · Accepted solution]

Hi @mgrayTCB 

Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBNCoAgEAbQu7h2MaOodRZpEVEQhAup+zdORpHT6kMfzo8xKlRaLWsaN0oD2FGEoAbdSE/hnCAGKLwo5WgfmY6c57S/6oFoV8UfKxdBNp7eezbTTo8AAnEzJxPPX8lKC/ja7YtcNNSPbBDvl8MJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjID = _t, Status = _t, Year = _t, Amt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjID", Int64.Type}, {"Status", type text}, {"Year", Int64.Type}, {"Amt", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ProjID"}, {{"Result", each List.Max(List.Distinct([Status])), type nullable text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ProjID"}, #"Grouped Rows", {"ProjID"}, "merge_", JoinKind.LeftOuter),
    #"Expanded merge_" = Table.ExpandTableColumn(#"Merged Queries", "merge_", {"Result"}, {"Result"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded merge_", each ([Result] = [Status])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Result"})
in
    #"Removed Columns"

Please accept the solution 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.

[mgrayTCB]

Hi,

Follow up problem. It turns out that my sample data was a bit too simplistic. I see you are using list.max for the status field to id the "final" instead of "current" but some of the projects also have and "other" status in that case I think list.max is getting the wrong value since "other" sorts after "final".  I tried to modify your M code to incorporate some soft of if statement but keep getting stuck. So the general logic is, if there is a final get that, otherwise get current. Always ignore other. Is there a better way to do this so I am not relying on sorting of text?

See revised sample data

ProjectID	Status	Year	Amt
1	final	2018	77
1	final	2019	55
1	final	2020	65
1	final	2021	35
1	current	2019	50
1	current	2020	60
1	current	2021	70
1	current	2018	66
1	other	2019	99
1	other	2020	88
1	other	2021	44
1	other	2018	55
2	final	2019	100
2	final	2020	500
2	final	2021	300
3	current	2019	666
3	current	2020	777
3	current	2021	666

[mgrayTCB]

Thank you. That is a super helpful example of the logic