checking for a condition and creating a table consisting a single row

[animeshsingh737]

Case 1 table -

Date	Name	Cost	Part
21-11-2023	historical	20	5
22-11-2023	historical	30	6
23-11-2023	historical	40	7
24-11-2023	historical	50	8
25-11-2023	historical	60	9
26-11-2023	historical	70	10
27-11-2023	historical	80	11
28-11-2023	historical	90	12
	predicted
	lower
	upper

Case 2 Table : 

Date	Name	Cost	Part
21-11-2023	historical	20	5
22-11-2023	historical	30	6
23-11-2023	historical	40	7
24-11-2023	historical	50	8
25-11-2023	historical	60	9
26-11-2023	historical	70	10
27-11-2023	historical	80	11
28-11-2023	historical	90	12
29-11-2023	forecast	100	13
29-11-2023	lower	98	12.5
29-11-2023	upper	102	13.5
30-11-2023	predicted	110	14
30-11-2023	lower	108	13.5
30-11-2023	upper	112	14.5

Case 3 table

Date	Name	Cost	Part
29-11-2023	predicted	100	10
29-11-2023	lower	98	8
29-11-2023	upper	102	12
30-11-2023	predicted	110	12
30-11-2023	lower	108	10
30-11-2023	upper	112	14

I am new to Power BI  but I need to do this report. Above is a table and I want to create a table with a only row selected from this table. 

The conditions are:

1. If there is no cost data for the predicted, lower, and upper names and only the historical name has cost data, then I want to select the last "historical" name row and create a table with that single row. (refer to first case table)
2. If there are the predicted, lower, and upper names and corresponding cost data are also there then I want to select the last row with the "historical" name and create a table with that single row.
3. If there are not any single rows of "historical", then, in that case, I want a table with the first row with the predicted name.

One point to be noted is that in the first case, the date value is also not there for the predicted, upper, and lower names.

I am using power query to get the data with post request so it would be nice if I could use this calculation after retrieving the data in the power query itself.

Expected outcome:

In the first case : 

28-11-2023

historical

90

12

In the second case : 

28-11-2023

historical

90

12

In the third case :

29-11-2023

predicted

100

10

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[animeshsingh737]

Thanks for pointing out the mistake. I am new to this forum but I updated the question and added the sample data in a table. Hope this will provide more clarity

[lbendlin]

for both scenarios you can apply these rules

- filter out all rows without values

- filter out all rows where the name is "upper" or "lower"

- keep bottom 1 row.

[animeshsingh737]

This is just a sample data. In the second case, there can be multiple dates with forecast, lower and upper. I want the first forecast for that. But in the first case, if upper, lower, and forecast are empty in a row then there will be no further rows.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[animeshsingh737]

I updated the question. I added a new condition for more clarity.

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc3dCoMwDIbhe+mxjib9sV6LeCDimCBU1LHbX5NsY1ByEgg8fO8wGIQWoEWLzjTmsZ5XPtZ52sqDtpxgxqYg1JAjFAU5DXlCnSCvoUAoCQoaioR6QVFDHSGwojpNJVYgKmmqZ4Wi+j91z8cyT+fFKUauRlt+LQetJF65hZo8950JWOSRj3FWaQG3fI2+LbBJHfrFgGOezPgG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Cost = _t, Part = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Name", type text}, {"Cost", Int64.Type}, {"Part", type number}}),
    Historicals = Table.SelectRows(#"Changed Type", each ([Name] = "historical")),
    Forecast = Table.SelectRows(#"Changed Type", each ([Name] = "forecast"))
in
    if Table.RowCount(Historicals) >0 then Table.LastN(Historicals,1) else Table.FirstN(Forecast,1)