Forum Discussion

animeshsingh737's avatar
animeshsingh737
Frequent Visitor
2 years ago
Solved

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

Case 1 table -

DateNameCostPart
21-11-2023historical205
22-11-2023historical306
23-11-2023historical407
24-11-2023historical508
25-11-2023historical609
26-11-2023historical70

10

 

27-11-2023historical8011
28-11-2023historical9012
 predicted  
 lower  
 upper  

 

Case 2 Table : 

DateNameCostPart
21-11-2023historical205
22-11-2023historical306
23-11-2023historical407
24-11-2023historical508
25-11-2023historical609
26-11-2023historical7010
27-11-2023historical8011
28-11-2023historical9012
29-11-2023forecast10013
29-11-2023lower9812.5
29-11-2023upper102

13.5

30-11-2023predicted110

14

30-11-2023lower108

13.5

30-11-2023upper112

14.5

 

Case 3 table

 

DateNameCostPart
29-11-2023predicted10010
29-11-2023lower988
29-11-2023upper10212
30-11-2023predicted11012
30-11-2023lower10810
30-11-2023upper11214

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-2023historical90

12

 

In the second case : 

28-11-2023historical9012

 

In the third case :

29-11-2023predicted10010
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago
    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)

7 Replies